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 SELECT, UPDATE 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;
Code language: SQL (Structured Query Language) (sql)
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 in 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:
Operator | Meaning |
= | equal |
> | greater than |
< | less than |
>= | greater than or equal |
<= | less than or equal |
<> | not equal |
Suppose you want to find an employee whose last name is King
, you can perform the following query:
SELECT
lastname, firstname, title
FROM
employees
WHERE
lastname = 'King';
Code language: SQL (Structured Query Language) (sql)
The database engine performs the following steps:
- First, examine the rows in
employees
table specified in theFROM
clause. - Second, get only rows whose value of the
lastname
column isKing
. - Third, return only columns available in the
SELECT
clause:lastname
,firstname
andtitle
.
To find employees who are located 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: Smalltalk (smalltalk)
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: SQL (Structured Query Language) (sql)
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: SQL (Structured Query Language) (sql)
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 are 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: SQL (Structured Query Language) (sql)
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 are evaluated to FALSE
For example, to find employees who are located 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: SQL (Structured Query Language) (sql)
SQL WHERE with NOT operator
To reverse the result of a condition, you use the NOT
operator. For example, to find employees who are not located 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: SQL (Structured Query Language) (sql)
When you use more than one logical operator in the WHERE
clause, the 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 WHERE
clause.
In this tutorial, we have shown you how to use the WHERE
clause to filter rows in result sets.