SQL WHERE

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;

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

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

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.

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.

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

The following query finds all employees who work in the department id 5.

SELECT
    employee_id,
    first_name,
    last_name,
    department_id
FROM
    employees
WHERE
    department_id = 5
ORDER BY
    first_name;

SQL WHERE clause with characters example

SQL is case-insensitive. However, when it comes to the values in the comparisons, it is case-sensitive. For instance, the following query finds the employee whose last name is Chen.

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    last_name = 'Chen';

However, if you use CHEN or chen, no row will be returned.

SQL WHERE clause with dates examples

To get all employees who joined the company after January 1st, 1999, you use the following query:

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date
FROM
    employees
WHERE
    hire_date >= '1999-01-01'
ORDER BY
    hire_date DESC;

If you want to find the employees who joined the company in 1999, you have several ways:

1. Use the YEAR function to get the year data from the hire_date column and use the equal to (=) operator to form the expression.
2. Use two expressions with the AND operator.
3. Use the BETWEEN operator.

The following statement illustrates the first way:

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date
FROM
    employees
WHERE
    YEAR (hire_date) = 1999
ORDER BY
    hire_date DESC;