Filter Rows in Result Sets Using SQL WHERE Clause

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:

The database engine evaluates the condition in the WHERE clause first 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 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 employee whose last name is King, you can perform the following query:

SQL WHERE equal operator example

The database engine performs the following steps:

  • First, examines the rows in employees table specified in the FROM clause.
  • Second, gets only rows whose value of the lastname column is King.
  • Third, returns 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:

SQL WHERE not equal operator example

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

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:

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 locate in the USA, you can use the AND operator as follows:

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 evaluates to FALSE.

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

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 does not locate in London or Seattle city, you use the NOT operator as follows:

SQL WHERE NOT operator example

When you use more than one logical operator in the WHERE clause, database engine evaluates the NOT operator first, 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 WHERE clause.

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