SQL WHERE Clause Explained with Operators and Examples

You need a handful of rows out of a table that holds thousands, and a SELECT without a filter hands you all of them. WHERE is the clause that cuts the result down. You write a condition, the database tests every row against it, and the rows for which that condition comes out TRUE are the rows you get back.

What is the WHERE clause?

The clause goes after FROM and before anything that sorts or groups the result:

SELECT column1, column2
FROM table_name
WHERE condition;

The same clause works in UPDATE and DELETE, where it decides which rows are changed or removed rather than which are returned. Its position in the statement never moves, so the SQL syntax of a filtered query is the unfiltered one with a line added.

The demo table

Every query below runs against one table of eight customers:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name       VARCHAR(50),
    Age        INT,
    City       VARCHAR(50)
);

INSERT INTO Customers VALUES
    (1, 'John',  28, 'New York'),
    (2, 'Alice', 30, 'London'),
    (3, 'James', 32, 'New York'),
    (4, 'Clara', 27, 'Paris'),
    (5, 'Bob',   35, 'London'),
    (6, 'Jules', 22, 'Berlin'),
    (7, 'Jimmy', 45, 'Los Angeles'),
    (8, 'Nora',  29, NULL);

SELECT * FROM Customers;

Nora has no city on file. That gap comes back in two of the sections below.

Comparison operators

The comparison operators are =, >, <, >=, <=, and <> for "not equal", which most databases also accept as !=. Each one compares a column against a value:

SELECT * FROM Customers
WHERE Age > 30;

Text is compared the same way, with the value in single quotes, as in WHERE City = 'London'.

BETWEEN, IN and LIKE

Three operators cover the conditions that a single comparison handles badly: a range, a list of accepted values, and a partial string.

BETWEEN takes two bounds and includes both of them, so the query below keeps the 35-year-old as well:

SELECT * FROM Customers
WHERE Age BETWEEN 25 AND 35;

IN takes a list and matches a row when the column equals any entry in it, which is shorter than chaining OR:

SELECT * FROM Customers
WHERE City IN ('London', 'Paris');

LIKE matches a pattern, in which % stands for any number of characters and _ stands for exactly one:

SELECT * FROM Customers
WHERE City LIKE 'L%';

Los Angeles is in that result because L% asks for a city starting with L, not for a city called London.

Filtering on NULL values

A NULL value means the value is unknown, and an unknown compares equal to nothing, not even to another unknown. WHERE City = NULL therefore returns no rows at all, whatever the table holds. The operator that finds the gaps is IS NULL:

SELECT * FROM Customers
WHERE City IS NULL;

IS NOT NULL returns the other seven rows. The same rule is what kept Nora out of the IN and LIKE results above: her city is unknown, so it matched neither list nor pattern.

AND, OR and NOT in the same condition

AND keeps a row only when both of its conditions are TRUE:

SELECT * FROM Customers
WHERE Age > 25 AND City = 'New York';

OR keeps a row when either condition is TRUE, and NOT inverts the condition that follows it. Inverting has one catch:

SELECT * FROM Customers
WHERE NOT City = 'London';

Nora is not in London, yet she is missing from the result. City = 'London' is unknown for her rather than FALSE, and inverting an unknown leaves it unknown, so the row is dropped.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Common mistakes

AND binds tighter than OR, the way multiplication binds tighter than addition. Mixing the two without parentheses gives a result that reads right and is wrong:

SELECT * FROM Customers
WHERE City = 'London' OR City = 'Paris' AND Age > 30;

The database read that as London, or else Paris with an age over 30. Clara is in Paris but 27, so no Paris row survives, while both Londoners come back regardless of age. Parentheses say what you meant:

SELECT * FROM Customers
WHERE (City = 'London' OR City = 'Paris') AND Age > 30;

Writing = NULL instead of IS NULL is the second classic, and it returns an empty result rather than an error, as the NULL section above shows. The third is a condition on an aggregate: WHERE runs on single rows before any grouping happens, so a test on COUNT() or SUM() belongs in the HAVING clause instead.

Practice questions

Four conditions to write against the Customers table above:

1. The customers whose name starts with J.
2. The customers in New York who are older than 25.
3. The customers whose age is not between 25 and 35.
4. The customers whose city is neither London nor Paris, Nora included.

The fourth one is the interesting one, because Nora's city is unknown and the NOT result above shows what an unknown does to a condition that excludes values.

If the rest of the statement is still new, the SQL tutorial for beginners walks through SELECT itself.

Download DbSchema at https://dbschema.com/download.html, connect to a database of your own, and rewrite these conditions against a real table in the SQL Editor. The connection, the diagram DbSchema draws from it, and the SQL Editor are all in the free Community Edition.

Frequently asked questions

What is the difference between the WHERE and HAVING clause in SQL?

WHERE is written before GROUP BY and tests one row at a time, using only the values in that row. HAVING is written after GROUP BY and tests the groups it produced, which is why an aggregate such as COUNT(*) or SUM(Age) can appear there and not in WHERE.

Can we use the WHERE clause with the UPDATE and DELETE commands?

Yes, and it means the same thing in all three statements: the rows whose condition is TRUE are the rows affected. An UPDATE or a DELETE written without a WHERE clause hits every row in the table.