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 certain 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
WHERE
    condition;

The WHERE the clause appears immediately after the FROM clause. The WHERE the 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 the 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 the 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;

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 the 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 the 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;

In this tutorial, we have shown you how to use the SQL WHERE clause to filter data based on a specified condition.