Filtering rows: WHERE in full

WHERE keeps the rows that meet a condition. Here you will combine conditions, match ranges and patterns, and handle NULL correctly.

Combining conditions

AND keeps rows when both conditions are true; OR keeps rows when either is true. This query looks for paid or shipped orders worth more than 500.

1

SELECT order_number, status, total
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND total > 500
ORDER BY total DESC
LIMIT 20;
Run in Planche

Result — first 5 of 20 rows

order_numberstatustotal
ORD-2025-002767shipped30906.66
ORD-2022-001901shipped28151.01
ORD-2023-001772paid26797.87
ORD-2024-001907shipped26648.91
ORD-2025-002847paid25837.57

Opens Planche in a separate browser tab, with the sample database loaded and this query ready. Every example on the page reuses that tab, and nothing runs until you press F9.

The parentheses change the meaning. Run the same conditions without them:

2

-- The same words without the brackets. AND binds tighter than OR,
-- so this reads as: paid (any total) OR shipped-and-over-500.
SELECT order_number, status, total
FROM orders
WHERE status = 'paid' OR status = 'shipped' AND total > 500
ORDER BY total DESC
LIMIT 20;
Run in Planche

Result — first 5 of 20 rows

order_numberstatustotal
ORD-2025-002767shipped30906.66
ORD-2022-001901shipped28151.01
ORD-2023-001772paid26797.87
ORD-2024-001907shipped26648.91
ORD-2025-002847paid25837.57

The result changes because AND has higher precedence than OR, just as × has higher precedence than +. Without parentheses, the database reads the condition as “paid at any total, or shipped and over 500,” so small paid orders are included too.

Matching one of several values

Use IN when the same column is compared with several values using OR.

3

SELECT order_number, status, total, ordered_at
FROM orders
WHERE status IN ('paid', 'shipped', 'delivered')
ORDER BY ordered_at DESC
LIMIT 20;
Run in Planche

Result — first 5 of 20 rows

order_numberstatustotalordered_at
ORD-2025-002480shipped6333.772025-12-28 23:52:14
ORD-2025-000505delivered2276.512025-12-28 11:16:39
ORD-2025-001581delivered2645.062025-12-28 06:12:38
ORD-2025-000995delivered2584.022025-12-28 05:47:53
ORD-2025-003219delivered561.482025-12-28 03:20:37

status IN ('paid', 'shipped', 'delivered') is equivalent to three OR conditions on status. NOT IN is the inverse, but it needs care when NULL is involved; the subqueries lesson covers that case.

Ranges

BETWEEN a AND b is shorthand for >= a AND <= b. Both ends are included.

4

SELECT order_number, ordered_at, total
FROM orders
WHERE ordered_at BETWEEN '2025-01-01' AND '2025-01-31 23:59:59'
ORDER BY ordered_at
LIMIT 20;
Run in Planche

Result — first 5 of 20 rows

order_numberordered_attotal
ORD-2025-0034122025-01-01 00:18:522806.57
ORD-2025-0036162025-01-01 11:18:146955.62
ORD-2025-0015032025-01-01 23:43:345837.23
ORD-2025-0003172025-01-02 05:08:3215130.7
ORD-2025-0012982025-01-02 18:36:325045.04

Patterns

LIKE compares against a pattern where % stands for any run of characters and _ for exactly one.

5

SELECT first_name, last_name, email
FROM customers
WHERE email LIKE '%@inbox.example'
ORDER BY last_name
LIMIT 20;
Run in Planche

Result — first 5 of 20 rows

first_namelast_nameemail
FreyaAhmedfreya.ahmed@inbox.example
HugoAhmedhugo.ahmed@inbox.example
JonasAhmedjonas.ahmed@inbox.example
KenjiAhmedkenji.ahmed@inbox.example
LiamAhmedliam.ahmed@inbox.example

Missing values

279 of these customers have no phone number recorded. Here they are:

6

SELECT id, first_name, last_name, phone
FROM customers
WHERE phone IS NULL
LIMIT 20;
Run in Planche

Result — first 5 of 20 rows

idfirst_namelast_namephone
2MateoCostaNULL
9SvenSmithNULL
21LaylaFischerNULL
25FreyaNguyenNULL
28PriyaJangNULL

And here is the query most people write first:

7

-- 279 customers have no phone number, and this finds none of them.
SELECT id, first_name, last_name, phone
FROM customers
WHERE phone = NULL;
Run in Planche

No rows

The result is an empty grid, with no error or warning.

NULL represents an unknown or missing value, so ordinary comparisons with it cannot be true. phone = NULL produces an unknown result, not true, and therefore matches no rows. NULL = NULL is not true either. Use IS NULL and IS NOT NULL instead.

What you can do now

You can express almost any condition over one table. The remaining limit is still that everything lives in one table — and the questions worth asking usually do not.

Try it yourself

  1. Customers in Korea or Japan who agreed to marketing email (marketing_opt_in is 1), sorted by country and then surname.

    Show the answer

    Show the answer

    SELECT first_name, last_name, country
FROM customers
WHERE country IN ('KR', 'JP')
  AND marketing_opt_in = 1
ORDER BY country, last_name
LIMIT 20;
    Run in Planche

    Result — first 5 of 20 rows

    first_namelast_namecountry
    AishaAhmedJP
    IsabellaAhmedJP
    IsabellaBrownJP
    TheoBrownJP
    NoraChoiJP

    IN saves an OR, and no brackets are needed here because there is only one AND joining two conditions. Two sort keys give country first, surname within it.

  2. Orders placed during 2024 that ended up cancelled or refunded, oldest first.

    Show the answer

    Show the answer

    SELECT order_number, status, ordered_at
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
  AND status IN ('cancelled', 'refunded')
ORDER BY ordered_at
LIMIT 20;
    Run in Planche

    Result — first 5 of 20 rows

    order_numberstatusordered_at
    ORD-2024-002563cancelled2024-01-02 11:41:56
    ORD-2024-002814cancelled2024-01-08 06:10:29
    ORD-2024-003123refunded2024-01-10 15:59:33
    ORD-2024-003024refunded2024-01-11 19:36:33
    ORD-2024-002258refunded2024-01-12 15:43:55

    The half-open range is the point: >= '2024-01-01' AND < '2025-01-01' covers the whole year including the last moment of 31 December, which BETWEEN '2024-01-01' AND '2024-12-31' would cut off at midnight.