What is WHERE in SQL? Row filtering for beginners

WHERE is a row filter. Without it, your query returns every row in the table. With it — only the rows you actually want.

Think of a table as a closet full of clothes. SELECT * FROM clothes means "give me everything in there". SELECT * FROM clothes WHERE color = 'red' means "give me just the red items". WHERE is that filter.

Why WHERE matters

Real-world tables hold tens of thousands — sometimes millions — of rows. Returning all of them is:

• Slow: the database does more work and the network ships more data.
• Pointless: you usually want specific users, orders, or products — not the whole table.

WHERE cuts out the noise on the database side before the data gets to you. It's one of the first tools every SQL developer and analyst learns — without it you can't go far.

How it works under the hood

When you write SELECT ... FROM users WHERE age > 18, the database does a simple thing:

1. Reads each row of users one by one.
2. Plugs the row's age value into the condition age > 18.
3. If the condition is TRUE — the row goes into the result. If FALSE — it's dropped.

WHERE is just a yes/no test applied to every row.

Basic syntax

SELECT name, age
FROM users
WHERE age > 18;

Line by line:

• SELECT name, age — which columns you want back.
• FROM users — which table.
• WHERE age > 18 — which rows to keep (only people older than 18).

The result is a list of adult users, with just their names and ages (no email, phone, or anything else we didn't ask for).

What you can put after WHERE

1. Comparisons

• = — equal
• <> or != — not equal
• >, <, >=, <= — greater than, less than, etc.

SELECT * FROM products WHERE price > 1000;
SELECT * FROM users    WHERE country = 'Russia';
SELECT * FROM orders   WHERE status <> 'cancelled';

Worked example. Given products:

id	name	price
1	Mug	350
2	Kettle	1200
3	Microwave	5000
4	Spoon	80

Query:

SELECT name, price FROM products WHERE price > 1000;

Result:

name	price
Kettle	1200
Microwave	5000

Mug and Spoon dropped out — their price is below 1000.

2. Logical operators AND, OR, NOT

• AND — both conditions must be true
• OR — at least one must be true
• NOT — negates a condition

SELECT * FROM users
WHERE country = 'Russia' AND age >= 21;

SELECT * FROM orders
WHERE status = 'paid' OR status = 'shipped';

SELECT * FROM products
WHERE NOT in_stock;

AND vs OR, hands-on. users table:

id	name	country	age
1	Anna	Russia	25
2	Bob	USA	30
3	Vera	Russia	17
4	Grisha	Russia	40

Query with AND (both conditions):

SELECT name FROM users WHERE country = 'Russia' AND age >= 21;

Result:

name
Anna
Grisha

Vera dropped (17, below 21). Bob dropped (not Russia). Only Anna and Grisha satisfy both conditions at the same time.

Same query with OR (at least one):

SELECT name FROM users WHERE country = 'Russia' OR age >= 21;

Result:

name
Anna
Bob
Vera
Grisha

Now all four match. Anna, Vera, Grisha — Russia. Bob — 30 (≥ 21). Each row has at least one true condition. Sobering difference between AND and OR.

3. A set of values: IN

When you want to check whether a value is one of several options:

SELECT * FROM users
WHERE country IN ('Russia', 'Belarus', 'Kazakhstan');

This is shorthand for:

WHERE country = 'Russia' OR country = 'Belarus' OR country = 'Kazakhstan'

IN reads cleaner, especially when the list grows.

There's also NOT IN:

SELECT * FROM users WHERE country NOT IN ('USA', 'Canada');

4. A range: BETWEEN

Great for dates, numbers — anything orderable.

SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

Important: BETWEEN is inclusive on both ends. BETWEEN 100 AND 500 is the same as >= 100 AND <= 500. Exact 100 and exact 500 both make it in.

5. Text search: LIKE

For partial string matches:

-- Everyone with a gmail address
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Names that start with 'A'
SELECT * FROM users WHERE name LIKE 'A%';

-- Names that contain 'nn' anywhere
SELECT * FROM users WHERE name LIKE '%nn%';

Two wildcards in LIKE:

• % — any number of any characters (including zero)
• _ — exactly one character

Example with _: WHERE phone LIKE '+1 555___' — phones starting with +1 555 followed by any three characters.

6. NULL is its own thing

This is the most common beginner trap. NULL in SQL is not a value — it's the absence of one. "Unknown". "Not filled in".

And you cannot compare it with =. Anywhere. Even NULL = NULL returns not TRUE, but NULL again ("unknown equals unknown — who knows").

-- WRONG: returns nothing, even if NULLs exist
SELECT * FROM users WHERE deleted_at = NULL;

-- RIGHT
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Remember: for NULL — only IS NULL and IS NOT NULL, never = or <>.

Bigger example: before and after

Given orders:

id	user_id	amount	status	country
1	10	500	paid	Russia
2	11	1500	paid	USA
3	12	200	cancelled	Russia
4	13	3000	paid	Russia
5	14	800	pending	Russia
6	15	4500	paid	Belarus

Goal: "paid orders from Russia, amount above 1000."

Query:

SELECT id, amount
FROM orders
WHERE status = 'paid'
  AND country = 'Russia'
  AND amount > 1000;

Result:

id	amount
4	3000

Who got dropped and why:

• #1: paid and Russian, but amount 500 — fails amount > 1000.
• #2: paid and big enough, but USA — not Russia.
• #3: cancelled — not paid.
• #5: pending — not paid.
• #6: paid and big enough, but Belarus — not Russia.

Only #4 satisfies all three at once (AND).

Now swap AND for OR on the same data:

SELECT id, amount, country, status
FROM orders
WHERE status = 'paid'
   OR country = 'Russia'
   OR amount > 1000;

Result:

id	amount	country	status
1	500	Russia	paid
2	1500	USA	paid
3	200	Russia	cancelled
4	3000	Russia	paid
5	800	Russia	pending
6	4500	Belarus	paid

All six rows make it. Each row hits at least one of the three conditions. A solid reminder of how different AND and OR are.

One more: dates and text

Same orders table, with dates and email:

id	created_at	email	amount
1	2024-03-10	anna@gmail.com	500
2	2024-06-22	bob@yandex.ru	1500
3	2024-11-05	vera@gmail.com	200
4	2025-01-15	grisha@mail.ru	3000

Goal: "orders from 2024, only gmail users."

SELECT id, email, amount
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND email LIKE '%@gmail.com';

Result:

id	email	amount
1	anna@gmail.com	500
3	vera@gmail.com	200

Order #2 is out (yandex.ru, not gmail). #4 is out (2025, not 2024).

Common beginner mistakes

1. Comparing NULL with =. Never works. Use IS NULL / IS NOT NULL.

2. Quote marks. Strings and dates go in single quotes '...'. Numbers don't need quotes. Double quotes "..." in PostgreSQL are for identifiers (column names), not strings.

-- Right
WHERE name = 'Alice' AND age = 30

-- Wrong: this looks for a column named Alice — error
WHERE name = "Alice"

3. Mixing up AND and OR. Always use parentheses in compound conditions to make precedence explicit:

-- You want: country Russia AND (men over 21 OR women over 18)
WHERE country = 'Russia'
  AND ((sex = 'M' AND age > 21) OR (sex = 'F' AND age > 18));

Without parentheses, the logic can land somewhere completely different from what you meant.

4. Filtering aggregates with WHERE. Doesn't work. WHERE runs before grouping and doesn't know about COUNT, SUM, etc. For aggregate conditions there's HAVING.

-- Wrong
SELECT country, COUNT(*) FROM users
GROUP BY country
WHERE COUNT(*) > 100;

-- Right
SELECT country, COUNT(*) FROM users
GROUP BY country
HAVING COUNT(*) > 100;

5. Case sensitivity. WHERE name = 'Alice' and WHERE name = 'alice' are different conditions in most databases. If case shouldn't matter, normalise both sides:

WHERE LOWER(name) = 'alice'

6. Forgetting AND between conditions. Multiple conditions need an explicit AND or OR — there's no implicit comma here.

-- Wrong (no AND)
WHERE country = 'Russia' age > 21

-- Right
WHERE country = 'Russia' AND age > 21

Quick recap

• WHERE filters rows in SELECT, UPDATE, and DELETE.
• Conditions use comparisons, AND/OR/NOT, IN, BETWEEN, LIKE.
• For NULL, use IS NULL / IS NOT NULL — regular comparisons silently fail.
• The tighter your WHERE, the less data the database returns — and the faster everything is.
• Always use parentheses in compound AND/OR conditions. Don't guess precedence, spell it out.

Try it yourself

Three exercises of growing difficulty on the same orders table:

1. Find all paid orders above 500.
2. Find orders from June 2024 by users outside Russia.
3. Find orders where delivered_at is not set yet (i.e. NULL).

Write the queries on your own first, then check yourself. If they don't click immediately — totally normal. SQL reads left to right like English, and clicks into automatic with reps.