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.
id | name | age | state | |
---|---|---|---|---|
1 | Brian | 15 | Michigan | brian@example.com |
2 | Leonard | 55 | Mississippi | leonard@example.com |
3 | Anvil | 31 | South Dakota | anvil@example.com |
4 | Jo | 44 | Maine | jo@example.com |
5 | Meredith | 43 | Delaware | meredith@example.com |
6 | Cody | 16 | Michigan | cody@example.com |
7 | Dilara | 50 | Ohio | dilara@example.com |
8 | Corbin | 47 | Wisconsin | corbin@example.com |
9 | Gin | 63 | Illinois | gin@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
11 | Zachary | 21 | Massachusetts | zachery@example.com |
12 | Delmar | 56 | Idaho | delmar@example.com |
13 | Dennie | 96 | Ohio | dennie@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
15 | Busrah | 18 | South Dakota | busrah@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
18 | Viola | 66 | Maine | viola@example.com |
19 | Nadya | 22 | Florida | nadya@example.com |
20 | Izabela | 61 | Arizona | izabela@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
2 | Leonard | 55 | Mississippi | leonard@example.com |
7 | Dilara | 50 | Ohio | dilara@example.com |
9 | Gin | 63 | Illinois | gin@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
12 | Delmar | 56 | Idaho | delmar@example.com |
13 | Dennie | 96 | Ohio | dennie@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
18 | Viola | 66 | Maine | viola@example.com |
20 | Izabela | 61 | Arizona | izabela@example.com |
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):
id | name | age | state | |
---|---|---|---|---|
1 | Brian | 15 | Michigan | brian@example.com |
2 | Leonard | 55 | Mississippi | leonard@example.com |
3 | Anvil | 31 | South Dakota | anvil@example.com |
4 | Jo | 44 | Maine | jo@example.com |
5 | Meredith | 43 | Delaware | meredith@example.com |
7 | Dilara | 50 | Ohio | dilara@example.com |
8 | Corbin | 47 | Wisconsin | corbin@example.com |
9 | Gin | 63 | Illinois | gin@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
11 | Zachary | 21 | Massachusetts | zachery@example.com |
12 | Delmar | 56 | Idaho | delmar@example.com |
13 | Dennie | 96 | Ohio | dennie@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
15 | Busrah | 18 | South Dakota | busrah@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
18 | Viola | 66 | Maine | viola@example.com |
19 | Nadya | 22 | Florida | nadya@example.com |
20 | Izabela | 61 | Arizona | izabela@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
1 | Brian | 15 | Michigan | brian@example.com |
2 | Leonard | 55 | Mississippi | leonard@example.com |
3 | Anvil | 32 | South Dakota | anvil@example.com |
4 | Jo | 44 | Maine | jo@example.com |
5 | Meredith | 43 | Delaware | meredith@example.com |
7 | Dilara | 50 | Ohio | dilara@example.com |
8 | Corbin | 47 | Wisconsin | corbin@example.com |
9 | Gin | 63 | Illinois | gin@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
11 | Zachary | 21 | Massachusetts | zachery@example.com |
12 | Delmar | 56 | Idaho | delmar@example.com |
13 | Dennie | 96 | Ohio | dennie@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
15 | Busrah | 18 | South Dakota | busrah@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
18 | Viola | 66 | Maine | viola@example.com |
19 | Nadya | 22 | Florida | nadya@example.com |
20 | Izabela | 61 | Arizona | izabela@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
3 | Anvil | 32 | South Dakota | anvil@example.com |
4 | Jo | 44 | Maine | jo@example.com |
5 | Meredith | 43 | Delaware | meredith@example.com |
7 | Dilara | 50 | Ohio | dilara@example.com |
8 | Corbin | 47 | WIsconsin | corbin@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
3 | Anvil | 32 | South Dakota | anvil@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
4 | Jo | 44 | Maine | jo@example.com |
5 | Meredith | 43 | Delaware | meredith@example.com |
11 | Zachery | 21 | Massachusetts | zachery@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
18 | Viola | 66 | Maine | viola@example.com |
19 | Nadya | 22 | Florida | nadya@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
1 | Brian | 15 | Michigan | brian@example.com |
2 | Leonard | 55 | Mississippi | leonard@example.com |
9 | Gin | 63 | Illinois | gin@example.com |
11 | Zachary | 21 | Massachusetts | zachery@example.com |
12 | Delmar | 56 | Idaho | delmar@example.com |
13 | Dennie | 96 | Ohio | dennie@example.com |
15 | Busrah | 18 | South Dakota | busrah@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
18 | Viola | 66 | Maine | viola@example.com |
19 | Nadya | 22 | Florida | nadya@example.com |
20 | Izabela | 61 | Arizona | izabela@example.com |
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:
id | name | age | state | |
---|---|---|---|---|
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
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).
id | name | age | state | |
---|---|---|---|---|
3 | Anvil | 32 | South Dakota | anvil@example.com |
10 | Alice | 50 | Nevada | alice@example.com |
13 | Dennie | 96 | Ohio | dennie@example.com |
14 | Aaron | 50 | Florida | aaron@example.com |
16 | Aveline | 88 | Nevada | aveline@example.com |
17 | Aherin | 72 | Arkansas | aherin@example.com |
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!