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.

IDNameAgeCityJobSalary
1Bob28New YorkManager60000
2Eve24New YorkDeveloper32000
3Max26New YorkJanitor9000
4Kim25NULLManager55000
5Joe23ChicagoDeveloper30000
6SamNULLChicagoJanitor10000

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;
IDNameAgeCityJobSalary
5Joe23ChicagoDeveloper30000

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';
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
2Eve24New YorkDeveloper32000
3Max26New YorkJanitor9000

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.

SQL conditional operators
OperatorDescriptionExample
=EqualWHERE x = 1
!=, <>Not equalWHERE x <> ‘a’ 
>Greater thanWHERE x > 1
<Less thanWHERE x < 1
>=Greater than or equalWHERE x >= 1
<=Less than or equalWHERE x <= 1
BETWEENChecks if a value lies within a rangeWHERE x BETWEEN 1 AND 10
EXISTSChecks if rows exist matching conditions that you specifyWHERE EXISTS(subquery)
INChecks if a value is contained in a set of specified valuesWHERE x IN (5, 10, 15, 20)
IS [NOT] NULLTests for nullityWHERE x IS NULL
LIKEChecks if a value matches a patternWHERE x LIKE ‘%abc_’

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;
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
4Kim25NULLManager55000

The next example lists all employees who are not developers.

SELECT *
FROM Employees
WHERE Job <> 'Developer';
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
3Max26New YorkJanitor9000
4Kim25NULLManager55000
6SamNULLChicagoJanitor10000

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';
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
3Max26New YorkJanitor9000

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';
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
2Eve24New YorkDeveloper32000
4Kim25NULLManager55000
5Joe23ChicagoDeveloper30000

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';
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
2Eve24New YorkDeveloper32000
3Max26New YorkJanitor9000

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;
IDNameAgeCityJobSalary
2Eve24New YorkDeveloper32000
5Joe23ChicagoDeveloper30000

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);
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
3Max26New YorkJanitor9000
5Joe23ChicagoDeveloper30000

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);
IDNameAgeCityJobSalary
2Eve24New YorkDeveloper32000
4Kim25NULLManager55000

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%';
IDNameAgeCityJobSalary
1Bob28New YorkManager60000
5Joe23ChicagoDeveloper30000

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;
IDNameAgeCityJobSalary
4Kim25NULLManager55000