SQL WHERE: filtering rows and combining conditions correctly

What a WHERE clause does

A table can hold millions of rows, but a query usually needs only the ones that meet a requirement. The WHERE clause describes that requirement: SQL checks it against every candidate row and keeps the row only when its condition is TRUE.

Suppose we need orders with an amount greater than 100.

SELECT order_id, customer, amount
FROM orders
WHERE amount > 100
ORDER BY order_id;

SQL applies the condition amount > 100 to each row:

Ben and Dev are left out because their amounts are not greater than 100.

Comparison operators

Most WHERE conditions begin with a comparison.

A comparison handles one condition at a time. Real requirements often involve several conditions, patterns, lists, or ranges, so SQL adds AND and OR, IN and NOT IN, LIKE, and BETWEEN. Let’s look into how each one works, with examples, common mistakes, and a quick quiz.

AND and OR

Use AND when every connected condition must be true; use OR when at least one may be true.

We’ll use this table throughout.

Using AND: every condition must be true

Problem: Find paid orders from India.

Solution: Here the country must be India and the status must be paid.

SELECT order_id, country, status
FROM orders
WHERE country = 'India'
  AND status = 'paid';

Only Order 1 is from India with a status equal to PAID, so only this one is included in the result.

Using OR: at least one condition may be true

Problem: To find all paid or pending orders:

SELECT order_id, country, status
FROM orders
WHERE status = 'paid'
   OR status = 'pending'
ORDER BY order_id;

Combining AND and OR: AND runs first

Now a more specific requirement: return paid or pending orders from India. A natural first attempt lists the three conditions in order. It is valid SQL and raises no error.

SELECT order_id, country, status
FROM orders
WHERE country = 'India'
  AND status = 'paid'
   OR status = 'pending';

Evaluate the grouped condition row by row and the stray UK order is obvious:

The database followed the query exactly. The query simply did not express the intended requirement, which is what makes a precedence mistake dangerous: it runs successfully while returning incorrect data.

Parenthesise the OR

The two allowed statuses belong together, so group them. The country condition now applies to both, and the equivalent IN form reads even more directly.

SELECT order_id, country, status
FROM orders
WHERE country = 'India'
  AND (status = 'paid' OR status = 'pending')
ORDER BY order_id;

-- same intent, harder to get wrong
WHERE country = 'India'
  AND status IN ('paid', 'pending')

Quick check: AND / OR

Requirement: high-priority tickets from the data team whose status is open or pending.

Three candidate conditions: which one expresses it correctly?

-- Option A
WHERE priority = 'high'
  AND team = 'data'
  AND status = 'open'
   OR status = 'pending'

-- Option B
WHERE priority = 'high'
  AND team = 'data'
  AND (status = 'open' OR status = 'pending')

-- Option C
WHERE (priority = 'high' AND team = 'data')
   OR (status = 'open' AND status = 'pending')

IN and NOT IN

Use IN when a column may match any value in a list. Instead of a chain of ORs:

WHERE status = 'paid'
   OR status = 'pending'
   OR status = 'processing'

write:

WHERE status IN ('paid', 'pending', 'processing')

Using the earlier orders table:

SELECT order_id, country, status
FROM orders
WHERE status IN ('paid', 'pending')
ORDER BY order_id;

IN performs exact-value matching; it does not do pattern matching. Use NOT IN when a value must not belong to a list.

The NOT IN + NULL trap

For a fixed list of known, non-null values, NOT IN is straightforward. The trap appears when the list comes from another table or subquery that can contain NULL.

We want customers who are not blocked. You might expect Asha and Carla.

SELECT customer_id, customer
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customers
);

For Asha, NOT IN (2, NULL) behaves like a chain of not-equals joined by AND:

1 <> 2      → TRUE
1 <> NULL   → UNKNOWN

TRUE AND UNKNOWN → UNKNOWN

WHERE UNKNOWN → row removed

The same thing happens to Carla. Because one value on the right is unknown, the whole condition is UNKNOWN, and the WHERE clause keeps only TRUE.

Safer approach: NOT EXISTS

When excluding rows based on a subquery, NOT EXISTS usually expresses the requirement more safely:

SELECT c.customer_id, c.customer
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers AS b
    WHERE b.customer_id = c.customer_id
);

NOT EXISTS asks a direct question for each customer: is there no blocked-customer row with the same id? The unrelated NULL in the blocked table does not make the condition unknown. You can also strip the nulls inside the subquery, but NOT EXISTS usually communicates the anti-matching intent more clearly:

WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customers
    WHERE customer_id IS NOT NULL
)

Two more IN gotchas

Quick check: NOT IN

SELECT order_id, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

LIKE

Use LIKE when you need to match a text pattern rather than one exact value. It uses two wildcards.

Begins with, ends with, contains

SELECT customer_id, customer
FROM customers
WHERE customer LIKE 'A%'
ORDER BY customer_id;

Because % matches zero or more characters, position matters:

LIKE 'an%'   -- begins with an
LIKE '%an'   -- ends with an
LIKE '%an%'  -- contains an anywhere

One character with the underscore

The underscore matches exactly one character. LIKE '_m%' means: any one character, then m, then anything, so Amit matches because its second character is m.

SELECT customer_id, customer
FROM customers
WHERE customer LIKE '_m%';

Use NOT LIKE to exclude a pattern. As with other comparisons, a NULL value produces UNKNOWN and is left out.

LIKE gotchas

WHERE code LIKE 'SAVE!_10' ESCAPE '!';

WHERE LOWER(customer) LIKE 'a%'

Quick check: LIKE

Which condition finds names whose second character is m?

-- Option A
WHERE customer LIKE 'm%'

-- Option B
WHERE customer LIKE '_m%'

-- Option C
WHERE customer LIKE '%_m'

BETWEEN

Use BETWEEN when a value must fall within a lower and upper boundary.

SELECT order_id, amount
FROM orders
WHERE amount BETWEEN 100 AND 300
ORDER BY order_id;

amount BETWEEN 100 AND 300 is equivalent to amount >= 100 AND amount <= 300.

BETWEEN gotchas

Timestamps: prefer a half-open range

BETWEEN is misleading on timestamp columns. Suppose created_at stores a date and a time:

To get every July order, BETWEEN '2026-07-01' AND '2026-07-31' looks reasonable, but the end may be read as 2026-07-31 00:00:00, which drops 2026-07-31 18:30:00. Use an inclusive start and an exclusive next boundary instead:

SELECT order_id, created_at
FROM orders
WHERE created_at >= '2026-07-01'
  AND created_at <  '2026-08-01';

Quick check: BETWEEN

WHERE amount BETWEEN 100 AND 300

Translating a business requirement

Most filtering mistakes happen before the SQL is written, when the requirement has not been divided into clear logical groups. Take:

Break it into separate decisions, then write one condition for each:

• Country must be India.
• Order time must fall within July.
• Status may be paid or pending.
• Amount must be from 100 through 500.

SELECT
    order_id,
    customer,
    country,
    status,
    amount,
    created_at
FROM orders
WHERE country = 'India'
  AND created_at >= '2026-07-01'
  AND created_at <  '2026-08-01'
  AND status IN ('paid', 'pending')
  AND amount BETWEEN 100 AND 500
ORDER BY order_id;

No parentheses are needed here because IN already groups its own list and every top-level condition is joined with AND. The query reads in the same order as the requirement (India, during July, paid or pending, amount 100 through 500), which makes it easy to verify.

Keep this mental model

A WHERE clause determines which rows are included, and a row is included only when the complete condition evaluates to TRUE. Pick the condition that fits the requirement:

And the gotchas worth remembering:

• AND is evaluated before OR; parentheses make logical groups explicit.
• IN matches exact values, not patterns.
• NOT IN can behave unexpectedly when its list contains NULL; prefer NOT EXISTS.
• % and _ are wildcards only with LIKE.
• BETWEEN includes both boundaries; write the lower one first.
• Timestamp ranges are safer with an inclusive start and an exclusive next boundary.