SQL WHERE

Summary: in this tutorial, you will learn how to use the SQL WHERE clause to filter rows based on specified conditions.

Introduction to SQL WHERE clause

To select specific rows from a table, you use a WHERE clause in the SELECT statement. The following illustrates the syntax of the WHERE clause in the SELECT statement:

SELECT 
    column1, column2, ...
FROM
    table_name
WHERE
    condition;
Code language: SQL (Structured Query Language) (sql)

The WHERE clause appears immediately after the FROM clause. The WHERE clause contains one or more logical expressions that evaluate each row in the table. If a row that causes the condition evaluates to true, it will be included in the result set; otherwise, it will be excluded.

Note that SQL has three-valued logic which are TRUE, FALSE, and UNKNOWN. It means that if a row causes the condition to evaluate to FALSE or NULL, the row will not be returned.

Note that the logical expression that follows the WHERE clause is also known as a predicate. You can use various operators to form the row selection criteria used in the WHERE clause.

The following table shows the SQL comparison operators:

Operator	Meaning
=	Equal to
<> (!=)	Not equal to
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal

To form a simple expression, you use one of the operators above with two operands that can be either column name on one side and a literal value on the other, for example:

salary > 1000
Code language: SQL (Structured Query Language) (sql)

It asks a question: “Is salary greater than 1000?”.

Or you can use column names on both sides of an operator such as:

min_salary < max_salary
Code language: SQL (Structured Query Language) (sql)

This expression asks another question: “Is the min salary less than the max salary?”.

The literal values that you use in an expression can be numbers, characters, dates, and times, depending on the format you use:

• Number: use a number that can be an integer or a decimal without any formatting e.g., 100, 200.5
• Character: use characters surrounded by either single or double quotes e.g., “100”, “John Doe”.
• Date: use the format that the database stores. It depends on the database system e.g., MySQL uses 'yyyy-mm-dd' format to store the date data.
• Time: use the format that the database system uses to store the time. For example, MySQL uses 'HH:MM:SS' to store time data.

Besides the SELECT statement, you can use the WHERE clause in the UPDATE or DELETE statement to specify which rows to be updated or deleted.

SQL WHERE examples

We will use the employees table to demonstrate how to select data from the table using the WHERE clause.

SQL WHERE clause with numeric comparison examples

The following query finds employees who have salaries greater than 14,000 and sorts the result set based on the salary in descending order.

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary > 14000
ORDER BY
	salary DESC;

Try It