Sometimes when you're working with SQL, you don't need to operate on an entire range of records. Or it would be really bad if you accidentally changed or deleted everything.

In these cases, you'll need to select only the part of the records on which you want to work, those that satisfy a certain condition. This is where SQL's WHERE clause is useful.

SQL WHERE Clause Syntax

You write the WHERE clause like this:

SELECT column1, column2...
FROM table_name
WHERE condition;

Note that here I've written it using the SELECT statement, but its use is not limited to SELECT. You can use it with other statements like DELETE and UPDATE as well.

SQL WHERE Clause in Action

Let's use this users table as an example of how to use the WHERE clause.

Example of SQL WHERE Clause with the SELECT Statement

When you want to make sure that a certain event will affect people that are 50 or above, you can select only those users with the following code:

SELECT *
FROM users
WHERE age >= 50;

This will give a table like below, that only lists the users who are 50 or above:

Example of SQL WHERE Clause with the DELETE Statement

Let's say that Cody has decided to remove himself from this list. You can update the table using a DELETE statement along with WHERE to make sure only Cody's record is deleted.

DELETE FROM users
WHERE name IS "Cody";

The users table will now look like below, without line 6 (where Cody's info was):

Example of SQL WHERE Clause with UPDATE Statement

Now perhaps you have received notice that Anvil has aged up and is now 32 years old. You can change Anvil's record using the UPDATE statement, and you can use WHERE to make sure that only Anvil's record gets updated.

UPDATE users
SET age = 32
WHERE name IS "Anvil";

Now the table will look like this:

Operators You Can Use with a WHERE Clause to Select Records

You can use operators like =, >, <, >=, <=, <> (or != depending on your SQL version), BETWEEN, LIKE, IN.

We have already seen >=, "greater than or equal to", in action in the examples above.

= is "equal to", > is "greater than", < is "smaller than", <= is "smaller than or equal to", <> (or !=) is "not equal to".

The four operators, greater than, smaller than, greater than or equal to, and smaller than or equal to are useful mostly when dealing with numbers.

The two operators, equal to, and not equal to, are useful both with numbers and and other data types.

How to Use the BETWEEN Operator in SQL

BETWEEN allows you to specify a range of numbers. For example WHERE age BETWEEN 24 and 51 will select all records in that age range.

SELECT * FROM users
WHERE age BETWEEN 24 AND 51;

There are 7 users with an age in this range:

How to Use the LIKE Operator in SQL

LIKE allows you to specify a pattern. For example WHERE name LIKE "A%" will select all records where the name starts with an A.

SELECT * FROM users
WHERE name LIKE "A%";

There are 5 users with a name that starts with A in our list:

How to make a pattern to use with LIKE

You can make a pattern using the characters % and _. The character % represents any number of characters (zero, one or more). The character _ represents exactly one character.

For example "_ook" could be "book", "look", "nook". But "%ook" could be also "ook" or "phonebook".

How to Use the IN Operator in SQL

IN lets you choose between a list of possibilities. For example let's see which users are on the East Coast.

SELECT * FROM users
WHERE state IN ("Maine", "New Hampshire", "Massachusetts", "Rhode Island", "Connecticut", "New York", "New Jersey", "Delaware", "Maryland", "Virginia", "North Carolina", "South Carolina", "Georgia", "Florida");

The IN operator is checking if the value in the state column is equal to one of the values in the list of East Coast states.

Only six of the users live on the East Coast:

Let's not forget about the IS, NOT, AND, OR Operators

We already used the IS operator in one of our examples above. Like WHERE name IS "Cody", it checks if a column has that exact value.

You can use NOT in front of a condition to make it the opposite. For example WHERE age NOT BETWEEN 24 AND 51 would select only users younger than 24 and older than 51. Using this criteria, 12 users are selected:

You use AND to combine conditions so that both have to be true, for example WHERE name LIKE "A%" AND age > 70 would select users with a name starting with A and that are older than 70. Only 2 users satisfy this criteria:

You can use OR to combine conditions so that only one of the two need to be true. For example WHERE name LIKE "A%" OR age > 70 would select users with a name starting with A or that are older than 70 (only one of the two parts has to be true, but both can also be true).

There are 6 users that have a name starting with A or are older than 70 years old (or both).

Conclusion

It's really important to specify on which records you want to operate in your tables.

With this article you have learned how to do so using the WHERE clause.

Thank you for reading!