SQL WHERE

Summary: in this tutorial, you will learn how to use the SQL WHERE clause in the SELECT statement to filter rows in a result set.

You use the WHERE clause in the SQL statements such as SELECTUPDATE and DELETE to filter rows that do not meet a specified condition.

The following illustrates the syntax of the WHERE clause in the SELECT statement:

SELECT select_list FROM table_name WHERE condition;

The SELECT statement evaluates the condition in the WHERE clause to eliminate rows that do not meet the condition. Based on the result, it then evaluates the SELECT clause to include only necessary columns into the final result set.

SQL provides various operators such as comparison operators, logical operators, etc., that allow you to construct the condition. Sometimes, the condition is called a predicate.

SQL WHERE with comparison operators

The following table illustrates the comparison operators in SQL:

OperatorMeaning
=equal
>greater than
<less than
>=greater than or equal
<=less than or equal
<>not equal

Suppose you want to find the employee whose last name is King, you can perform the following query:

SELECT lastname, firstname, title FROM employees WHERE lastname = 'King';
Code language: JavaScript (javascript)
SQL WHERE equal operator example

The database engine performs the following steps:

  • First, examine the rows in employees table specified in the FROM clause.
  • Second, get only rows whose value of the lastname column is King.
  • Third, return only columns available in the SELECT clause: lastname, firstname and title.

To find employees who locate in the USA, you can use the not equal operator (<>) in the WHERE clause as follows:

SELECT lastname, firstname, title, country FROM employees WHERE country <> 'USA'
Code language: HTML, XML (xml)
SQL WHERE not equal operator example

To find employees who joined the company before 1993, you can use less than operator (<) like the following query:

SELECT lastname, firstname, title, country, DATE(hiredate) FROM employees WHERE hiredate < '1993-01-01'
Code language: JavaScript (javascript)
SQL WHERE less than operator example

To find employees who joined the company after 1993, you use the greater-than operator ( > ) in the WHERE clause as follows:

SELECT lastname, firstname, title, country, DATE(hiredate) FROM employees WHERE hiredate > '1993-01-01'
Code language: JavaScript (javascript)
SQL WHERE greater than operator example

SQL WHERE with logical operators

SQL provides the following logical operators: AND, OR and NOT. You use the AND and OR operators to combine conditions in the WHERE clause, and the NOT operator to reverse the result of a condition.

SQL WHERE with AND operator

The AND operator combines two conditions and returns TRUE only if both conditions are TRUE.

Suppose you want to find employees who joined the company after 1993 and located in the USA, you can use the AND operator as follows:

SELECT lastname, firstname, title, country, DATE(hiredate) FROM employees WHERE hiredate > '1993-01-01' AND country = 'USA'
Code language: JavaScript (javascript)
SQL WHERE AND operator example

SQL WHERE with OR operator

The OR operator combines two conditions. It returns TRUE when at least a condition is TRUE and returns FALSE when all conditions evaluate to FALSE

For example, to find employees who locate in London or Seattle city, you can use the OR operator as follows:

SELECT firstname, lastname, city FROM employees WHERE city = 'London' OR city = 'Seattle'
Code language: JavaScript (javascript)
SQL WHERE OR operator example

SQL WHERE with NOT operator

To reverse the result of a condition, you use the NOT operator. For example, to find employees who do not locate in London or Seattle city, you use the NOT operator as follows:

SELECT firstname, lastname, city FROM employees WHERE NOT (city = 'London' OR city = 'Seattle')
Code language: JavaScript (javascript)
SQL WHERE NOT operator example

When you use more than one logical operator in the WHERE clause, database engine evaluates the NOT operator first, and then AND operator, and finally  OR operator. This is known as operator precedence.

To instruct the database engine to evaluate the operator based on a specified preference, you use parentheses like the query above i.e., it evaluates the  OR operator first and then the NOT operator.

Besides those operators, you can also use the BETWEEN, IN, LIKE, EXISTS, and IS operators in the WHEREclause.

In this tutorial, we have shown you how to use the WHEREclause to filter rows in result sets.