SQL WHERE Clause: Guide and Examples

In this article, you'll learn what the WHERE clause is in SQL, how to use it, and see some examples.

Description

The SQL WHERE clause allows you to filter the results used in your query. It lets you specify the rows that will be used.

The WHERE clause can be used with several statements:

• SELECT to restrict the rows that are returned
• UPDATE to restrict the rows that are updated
• DELETE to specify the rows that will be deleted

Syntax

The syntax for the WHERE clause is:

1WHERE condition

It looks simple, but the WHERE clause can be quite long and complex depending on your query and requirements.

Let's take a look at some examples.

DDL and DML Code for Examples

Here's the SQL code that we'll use for the examples. You can copy and paste this code into your SQL IDE and run it.

It's been designed for an Oracle database (e.g. it uses the VARCHAR2 data type) but it can be modified for other database vendors.

Our sample table shows a list of employees, and looks like this:

The SQL to create and populate this table looks like this:

 1CREATE TABLE employee (
 2id NUMBER(5),
 3last_name VARCHAR2(50),
 4salary NUMBER(8)
 5);
 6 
 7INSERT ALL
 8INTO employee (id, last_name, salary) VALUES (1, 'SMITH', 40000)
 9INTO employee (id, last_name, salary) VALUES (2, 'ANDERSON', 60000)
10INTO employee (id, last_name, salary) VALUES (3, 'JONES', 45000)
11INTO employee (id, last_name, salary) VALUES (4, 'LANDY', 82000)
12INTO employee (id, last_name, salary) VALUES (5, 'CHARLESTON', 21000)
13INTO employee (id, last_name, salary) VALUES (6, 'JOHNSON', 51000)
14INTO employee (id, last_name, salary) VALUES (7, 'BROWN', 93000)
15INTO employee (id, last_name, salary) VALUES (8, 'HARDEN', 40000)
16INTO employee (id, last_name, salary) VALUES (9, 'CONNOR', 52000)
17INTO employee (id, last_name, salary) VALUES (10, 'PIERCE', 39000)
18SELECT * FROM dual;

If you're interested in learning more about the INSERT statement, including how this INSERT ALL works, read this guide to the INSERT statement.

Let's look at the examples.

Example 1: Simple WHERE Clause

In this example, we'll use a simple SQL WHERE clause that shows all of the employees that have a salary of 40000.

Our SQL query would look like this:

1SELECT id, last_name, salary
2FROM employee
3WHERE salary = 40000;

We simply add the condition to the WHERE clause. In this example, the condition is where the salary column is equal to 40000.

The results of this query are below:

Only the two rows with a salary of 40000 (SMITH and HARDEN) are shown. The other rows are still in the database but are not returned by this query as they don't match the WHERE clause.

Example 2: WHERE Clause with Text

The WHERE clause can also use conditions with text values. This is done by specifying the text value within single quotes.

This query will show all employees where the last name is BROWN.

1SELECT id, last_name, salary
2FROM employee
3WHERE last_name = 'BROWN';

The results are:

Only one row is shown because only one row has a last_name of BROWN. The word BROWN is enclosed in single quotes in the query. Without the single quotes, Oracle will assume BROWN is a column or another object in the database, and show an error.

If you want to use single quotes in a name, you'll need to "escape" them. I've written an article on doing that here: How to Escape Single Quotes in SQL.

Example 3: WHERE Clause with Greater Than

The SQL WHERE clause doesn't always have to use an equals sign. You can use any of the other operators in a WHERE clause to show the data you want.

In this example, we will show all employees where the salary is greater than 40000.

1SELECT id, last_name, salary
2FROM employee
3WHERE salary > 40000;

The results of this query are:

This shows all employees with a salary greater than 40000. It doesn't show SMITH or HARDEN because their salaries are exactly 40000 and are excluded from the "greater than 40000" condition.

Example 4: WHERE Clause with Two Conditions and AND Keyword

The examples so far have shown a WHERE clause with one criteria. SQL allows you to combine two or more criteria into a single statement. This can be done in two ways:

• Showing rows where both criteria are true for the row
• Showing rows where either criteria are true for the row.

To show rows where both criteria are true, use the AND keyword in between both criteria.

For example, to see rows where the salary is greater than 40000 as well as less than 80000, your query could look like this:

1SELECT id, last_name, salary
2FROM employee
3WHERE salary > 40000
4AND salary < 80000;

The AND keyword appears after "salary > 40000" and before "salary < 80000". This means both conditions must be true for the record to be shown.

The results of this query are:

Only rows where the salary is greater than 40000 and less than 80000 are shown.

Example 5: WHERE Clause with Two Conditions OR

SQL allows you to check if either of two conditions are true and return a row. This is done using the OR keyword in between two criteria.

For example, to show employees who have a salary of less than 40000 or greater than 80000, you could use this query:

1SELECT id, last_name, salary
2FROM employee
3WHERE salary < 40000
4OR salary > 80000;

The results of this query are:

The rows only match one of the conditions (either less than 40000 or greater than 80000) and not both of the conditions.

Example 6: WHERE Clause with IN

The SQL WHERE clause can be used with multiple criteria as we've just seen. If you want to check if a column value is equal to one of many different values, you can use several OR keywords:

1SELECT id, last_name, salary
2FROM employee
3WHERE salary = 40000
4OR salary = 50000
5OR salary = 60000
6OR salary = 70000;

Depending on your requirements, your query could get quite long and repetitive. We're essentially writing the same rule with just the salary number that varies.

There is a better way to do this, and it's using the IN keyword.

The IN keyword in SQL lets you specify several values inside brackets, and the WHERE clause will check if any of them matches your column. If any of them do, then the row is displayed.

The same query above can be written using the IN keyword:

1SELECT id, last_name, salary
2FROM employee
3WHERE salary IN (40000, 50000, 60000, 70000);

It's a lot simpler and shorter. It's also easier to add more values to it if needed.

This query shows the following results:

Example 7: WHERE Clause with BETWEEN

If you want to check if a value is between two other values, you can use a combination of "greater than or equal to" and "less than or equal to".

1SELECT id, last_name, salary
2FROM employee
3WHERE salary <= 40000
4OR salary >= 80000;

Another way to do this which involves less code is to use the BETWEEN keyword. This BETWEEN keyword allows you to combine "greater than or equal to" and "less than or equal to" into a single clause, resulting in a simpler query.

The query above can be written like this:

1SELECT id, last_name, salary
2FROM employee
3WHERE salary BETWEEN 40000 AND 80000;

The BETWEEN keyword comes after the column you're comparing, and you specify two numbers separated by AND.

The results are:

Example 8: WHERE Clause with LIKE

So far we've looked at WHERE clauses that use an exact match, on either a text value or a number value.

SQL also allows you to do a partial match with the WHERE clause by using the LIKE operator.

The LIKE operator has been described here and can be written in a query like this:

1SELECT id, last_name, salary
2FROM employee
3WHERE last_name LIKE 'J%';

This will show all employees where the last_name starts with J.

The results are:

Only those records with a last_name starting with J are shown.

Example 9: UPDATE with WHERE

The WHERE clause can also be used in an UPDATE statement. This allows you to specify which rows in your table will be updated.

For example, to update the salary of employees with a salary of 40000 to 42000, your query would look like this:

1UPDATE employee
2SET salary = 42000
3WHERE salary = 40000;

After running this query, the rows that have a salary of 40000 will now have a salary of 42000.

Example 10: DELETE with WHERE

You can also use the WHERE clause in the DELETE statement. This will let you specify which rows to delete.

To delete all employees with a salary of more than 80000, your query would look like this:

1DELETE FROM employee
2WHERE salary > 80000;

The matching rows will be deleted from the table.

Conclusion

The WHERE clause is an important clause in SQL queries. It's used in the SELECT statement to specify which rows to show, and it's used in UPDATE and DELETE to specify the impacted rows.

This article on optimisation in MySQL may also be useful.