FB

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.

SQL WHERE

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:

WHERE 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:

ID LAST_NAME SALARY
1 SMITH 40000
2 ANDERSON 60000
3 JONES 45000
4 LANDY 82000
5 CHARLESTON 21000
6 JOHNSON 51000
7 BROWN 93000
8 HARDEN 40000
9 CONNOR 52000
10 PIERCE 39000

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

CREATE TABLE employee (
id NUMBER(5),
last_name VARCHAR2(50),
salary NUMBER(8)
);
 
INSERT ALL
INTO employee (id, last_name, salary) VALUES (1, 'SMITH', 40000)
INTO employee (id, last_name, salary) VALUES (2, 'ANDERSON', 60000)
INTO employee (id, last_name, salary) VALUES (3, 'JONES', 45000)
INTO employee (id, last_name, salary) VALUES (4, 'LANDY', 82000)
INTO employee (id, last_name, salary) VALUES (5, 'CHARLESTON', 21000)
INTO employee (id, last_name, salary) VALUES (6, 'JOHNSON', 51000)
INTO employee (id, last_name, salary) VALUES (7, 'BROWN', 93000)
INTO employee (id, last_name, salary) VALUES (8, 'HARDEN', 40000)
INTO employee (id, last_name, salary) VALUES (9, 'CONNOR', 52000)
INTO employee (id, last_name, salary) VALUES (10, 'PIERCE', 39000)
SELECT * 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:

SELECT id, last_name, salary
FROM employee
WHERE 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:

ID LAST_NAME SALARY
1 SMITH 40000
8 HARDEN 40000

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.

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

The results are:

ID LAST_NAME SALARY
7 BROWN 93000

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.

SELECT id, last_name, salary
FROM employee
WHERE salary > 40000;

The results of this query are:

ID LAST_NAME SALARY
2 ANDERSON 60000
3 JONES 45000
4 LANDY 82000
6 JOHNSON 51000
7 BROWN 93000
9 CONNOR 52000

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:

SELECT id, last_name, salary
FROM employee
WHERE salary > 40000
AND 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:

ID LAST_NAME SALARY
2 ANDERSON 60000
3 JONES 45000
6 JOHNSON 51000
9 CONNOR 52000

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:

SELECT id, last_name, salary
FROM employee
WHERE salary < 40000
OR salary > 80000;

The results of this query are:

ID LAST_NAME SALARY
4 LANDY 82000
5 CHARLESTON 21000
7 BROWN 93000
10 PIERCE 39000

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:

SELECT id, last_name, salary
FROM employee
WHERE salary = 40000
OR salary = 50000
OR salary = 60000
OR 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:

SELECT id, last_name, salary
FROM employee
WHERE 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:

ID LAST_NAME SALARY
1 SMITH 40000
2 ANDERSON 60000
8 HARDEN 40000

 

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”.

SELECT id, last_name, salary
FROM employee
WHERE salary <= 40000
OR 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:

SELECT id, last_name, salary
FROM employee
WHERE 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:

ID LAST_NAME SALARY
1 SMITH 40000
2 ANDERSON 60000
3 JONES 45000
6 JOHNSON 51000
8 HARDEN 40000
9 CONNOR 52000

 

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:

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

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

The results are:

ID LAST_NAME SALARY
3 JONES 45000
6 JOHNSON 51000

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:

UPDATE employee
SET salary = 42000
WHERE 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:

DELETE FROM employee
WHERE 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.

Get Your SQL Cheat Sheets Now: