SQL WHERE Clause

Database tables usually contain large amounts of data, and you rarely need to retrieve all the rows in a table. More often than not, you will want to retrieve the subset of the table’s data as needed for specific operations or reports. Retrieving the desired data involves specifying search criteria, also known as search condition.

The data is filtered by specifying the search condition in the WHERE clause within the SELECT statement. If the condition is true for a row, then that row is included in the result set.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition;

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Employee Management System’ that contains basic information about employees.

Using the WHERE Clause

The WHERE clause allows you to retrieve only rows you are interested in.

For example, perhaps you are interested in retrieving data from the ‘Employees’ table, but only those employees whose salary is $30,000. The following query employs the WHERE clause to retrieve only one employee record:

SELECT *
FROM Employees
WHERE Salary = 30000;

The rules for qualifying text fields follow the same structure as number fields. However, when using text, you must wrap literals (or text values you specify) in single quotes.

For example, if you wanted to view employees from ‘New York’, you could run this query:

SELECT *
FROM Employees
WHERE City = 'New York';

The WHERE Clause Operators

The above examples test only for equality – determining if a column contains a specific value. The WHERE clause doesn’t always have to test for equality. You can use other comparison operators to filter down records.

SQL supports a whole range of conditional operators.

You have already seen an example of testing for equality. Let’s take a look at some examples that demonstrate the use of other operators.

The first example lists all employees whose salary is more than $40,000

SELECT *
FROM Employees
WHERE Salary > 40000;

The next example lists all employees who are not developers.

SELECT *
FROM Employees
WHERE Job <> 'Developer';

Combining Conditions (with AND and OR operator)

Often, you’ll need to specify multiple conditions in a single WHERE clause, for example, to retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition.

The AND Operator

If two conditions are connected by the AND operator, rows are retrieved for which both conditions are true.

For example, if you wanted to select employees who are not ‘Developers’ and who live in ‘New York’ city, you could run this query:

SELECT *
FROM Employees
WHERE Job <> 'Developer' AND City = 'New York';

The first condition Job <> 'Developer' filtered out 4 of 6 employee rows, and the second condition City = 'New York' filtered out an additional 2 rows, leaving 2 rows in the final result set.

The OR Operator

If two conditions are connected by the OR operator, all rows of a table are retrieved in which either the first or the second condition (or both) is true.

For example, here is a query that selects only those employees who are either ‘Managers’ or ‘Developers’

SELECT *
FROM Employees
WHERE Job = 'Manager' OR Job = 'Developer';

Negating a Condition (with NOT operator)

NOT operator has one and only one function: negating whatever condition comes next. It retrieves the rows for which the specified condition is FALSE (NOT TRUE).

The NOT operator is never used by itself; It is always used in conjunction with other operators such as BETWEEN, ANY, AND, OR, or LIKE. That’s why the NOT keyword is used before the column name, and not after it.

For example, if you wanted to select all employees who are not from ‘Chicago’, you could write a query like:

SELECT *
FROM Employees
WHERE NOT City = 'Chicago';

Range Conditions

Along with checking against a single value, you can build conditions that check for a range of values. This type of condition is used when working with numerical or temporal data (data related to date and time).

To check whether an expression falls within a certain range, you can use the BETWEEN operator. Its syntax differs slightly from other WHERE clause operators because it requires two values – the beginning and the end of the range.

The BETWEEN operator can be used, for example, to find all employees whose salaries range between $30,000 to $40,000.

SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 and 40000;

Read more about the BETWEEN operator here.

Membership Conditions

In some cases you might want to check for an expression, not against a single value or range of values, but against a set of values. For example, you might want to find all employees who are either 23, 26 or 28 years old.

While this can be done by OR’ing three conditions together which is not very tedious, imagine if the set of expressions contained 10 or 20 values. For these situations, you can use the IN operator instead:

SELECT *
FROM Employees
WHERE Age IN(23, 26, 28);

If you want to see whether the expression does not exist within a set of expressions, you can use the NOT IN operator.

For example, the following query lists all employees who are neither 23, 26 nor 28 years old.

SELECT *
FROM Employees
WHERE Age NOT IN(23, 26, 28);

Read more about the IN operator here.

Matching Conditions

By now, you have been introduced to conditions that identify an exact string or a set of strings; the final condition type deals with partial string matches. For example, you may want to find all employees whose second letter of name is ‘o’.

When searching for such partial string matches, the LIKE operator is used. There are two wildcards often used in conjunction with the LIKE operator:

%    The percent sign matches zero or more characters

_     The underscore character matches just a single character

As an example let’s find all employees whose second letter of name is ‘o’.

SELECT *
FROM Employees
WHERE Name LIKE '_o%';

Read more about the LIKE operator here.

Checking for NULL Values

You may have noticed that some columns, such as ‘Age’ and ‘City’, have NULL values. A NULL is a value that has no value. It is nothing but an absence of value.

NULL values cannot be determined with an ‘= NULL’. You need to use the IS NULL or IS NOT NULL clauses to identify them. So, to get a list of employees with no recorded city, you could run this query:

SELECT *
FROM Employees
WHERE City IS NULL;