The WHERE Clause

SELECT tells SQL which columns to return. WHERE tells it which rows matter. Every business question you answer with data starts with filtering to the right subset — without correct filtering you're either analysing too much data or not enough.

Learning Objectives

• Filter rows using comparison operators
• Combine conditions with AND, OR, NOT
• Handle NULL values correctly
• Use BETWEEN, IN, and LIKE for common filtering patterns
• Identify and fix silent bugs caused by AND/OR precedence and NULL comparisons

---

Basic Filtering

-- Business question: which orders are worth more than £100?
SELECT
    order_id,
    customer_id,
    order_date,
    quantity * unit_price   AS order_total
FROM orders
WHERE quantity * unit_price > 100;

Sample output:

order_id	customer_id	order_date	order_total
O002	C003	2024-01-07	389.00
O001	C001	2024-01-05	298.00
O007	C003	2024-01-15	249.95

---

Comparison Operators

Operator	Meaning	Example
=	Equal to	WHERE status = 'completed'
<> or !=	Not equal to	WHERE status <> 'cancelled'
>	Greater than	WHERE quantity * unit_price > 100
<	Less than	WHERE quantity < 5
>=	Greater than or equal	WHERE order_date >= '2024-01-01'
<=	Less than or equal	WHERE unit_price <= 50.00

String comparison is case-sensitive in PostgreSQL

WHERE status = 'Completed' will NOT match 'completed' in PostgreSQL. MySQL with utf8mb4_unicode_ci collation is case-insensitive by default. Use LOWER(status) = 'completed' to be safe across databases.

Date literals must be quoted as strings

WHERE order_date >= 2024-01-01 is parsed as an arithmetic expression (2024 minus 1 minus 1 = 2022), not a date. Always quote date literals: WHERE order_date >= '2024-01-01'.

---

Combining Conditions

AND — both conditions must be true

-- Orders placed this year that are worth more than £100
SELECT order_id, customer_id, order_date, quantity * unit_price AS order_total
FROM orders
WHERE order_date >= '2024-01-01'
  AND quantity * unit_price > 100;

OR — at least one must be true

-- Orders that are either pending or still being processed
SELECT order_id, status
FROM orders
WHERE status = 'pending'
   OR status = 'processing';

NOT — negate a condition

-- All orders that are not cancelled
SELECT order_id, status, order_date
FROM orders
WHERE NOT status = 'cancelled';
-- Equivalent to: WHERE status <> 'cancelled'

Parentheses — always use them when mixing AND and OR

AND binds more tightly than OR. Without parentheses, you get wrong results without any error message.

-- Correct: high-value orders that are completed OR pending
SELECT order_id, quantity * unit_price AS order_total, status
FROM orders
WHERE quantity * unit_price > 500
  AND (status = 'completed' OR status = 'pending');

-- WRONG: reads as (order_total > 500 AND status = 'completed') OR (status = 'pending')
-- Returns ALL pending orders regardless of value — not what was asked
WHERE quantity * unit_price > 500 AND status = 'completed' OR status = 'pending';

Sample output (correct query):

order_id	order_total	status
O002	389.00	completed
O008	389.00	pending

Missing parentheses with AND/OR is one of the most common SQL bugs

A query that runs without error can still return completely wrong data. Always parenthesise when mixing AND and OR. When in doubt, add the parentheses.

---

Filtering Ranges — BETWEEN

BETWEEN is inclusive on both ends — it includes the boundary values.

-- Orders with a gross total between £100 and £400 (inclusive)
SELECT order_id, customer_id, quantity * unit_price AS order_total
FROM orders
WHERE quantity * unit_price BETWEEN 100 AND 400;
-- Same as: WHERE quantity * unit_price >= 100 AND quantity * unit_price <= 400

-- Orders placed in January 2024
SELECT order_id, order_date, status
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Sample output:

order_id	order_date	status
O001	2024-01-05	completed
O002	2024-01-07	completed
O003	2024-01-09	pending
O004	2024-01-10	completed

BETWEEN with datetime columns in MySQL

If order_date stores datetime values (not just dates), BETWEEN '2024-01-01' AND '2024-01-31' may miss rows from January 31 after midnight because it compares against '2024-01-31 00:00:00'. Use order_date < '2024-02-01' instead.

---

Filtering Lists — IN

-- Without IN — verbose and error-prone
WHERE status = 'pending' OR status = 'processing' OR status = 'on_hold';

-- With IN — clean and readable
WHERE status IN ('pending', 'processing', 'on_hold');

-- Exclude a list
WHERE status NOT IN ('cancelled', 'refunded');

Business use: find orders for specific products

SELECT order_id, product_id, order_date
FROM orders
WHERE product_id IN ('P001', 'P003', 'P005')
  AND status = 'completed';

NOT IN with NULLs returns zero rows

If the list passed to NOT IN contains a NULL value, the entire condition evaluates to UNKNOWN and returns nothing — even rows that should clearly pass the filter.

-- If any product_id in the subquery is NULL, this returns ZERO rows
WHERE product_id NOT IN (SELECT product_id FROM discontinued_products);

-- Safe alternative: filter NULLs out explicitly
WHERE product_id NOT IN (
    SELECT product_id FROM discontinued_products WHERE product_id IS NOT NULL
);

-- Even safer: use NOT EXISTS
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_products dp WHERE dp.product_id = orders.product_id
);

For mid-level analysts

IN with a subquery runs the subquery once and caches the result (non-correlated). On very large result sets, EXISTS is often faster because it short-circuits on the first match. Test both with EXPLAIN on your specific data.

---

Pattern Matching — LIKE

% matches any sequence of characters (including zero characters). _ matches exactly one character.

-- Customers with Gmail addresses
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@gmail.com';

-- Products starting with "Wireless"
SELECT product_id, product_name
FROM products
WHERE product_name LIKE 'Wireless%';

-- Find any order_id that is exactly 4 characters
WHERE order_id LIKE '____';   -- four underscores = exactly 4 chars

Sample output:

SELECT customer_id, first_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

customer_id	first_name	email
C001	Priya	priya@gmail.com
C003	Sarah	sarah@gmail.com

Leading wildcard forces a full table scan

LIKE '%keyword' or LIKE '%keyword%' cannot use a standard B-tree index on that column. The database must check every row. On tables with millions of rows, this is extremely slow.

For mid-level analysts

LIKE 'keyword%' (trailing wildcard only) can use an index. For suffix or substring search on large tables, use full-text search: MATCH(email) AGAINST('gmail.com') in MySQL, or to_tsvector + to_tsquery in PostgreSQL.

For senior analysts

If you need case-insensitive LIKE, MySQL's default collation handles it automatically. PostgreSQL requires ILIKE for case-insensitive matching or a functional index on LOWER(column).

---

Handling NULL Values

NULL means "unknown" or "not applicable." It is not zero. It is not an empty string. It propagates through all operations.

-- WRONG: always evaluates to UNKNOWN, returns zero rows
WHERE delivery_date = NULL;
WHERE delivery_date != NULL;

-- CORRECT
WHERE delivery_date IS NULL;       -- orders not yet delivered
WHERE delivery_date IS NOT NULL;   -- orders that have been delivered

Business use: find orders awaiting delivery

SELECT order_id, customer_id, order_date, status
FROM orders
WHERE delivery_date IS NULL
  AND status NOT IN ('cancelled', 'refunded')
ORDER BY order_date ASC;

Sample output:

order_id	customer_id	order_date	status
O003	C002	2024-01-09	pending
O007	C003	2024-01-15	pending
O008	C002	2024-01-18	pending

NULL comparisons always evaluate to UNKNOWN

NULL = NULL is UNKNOWN, not TRUE. NULL <> NULL is also UNKNOWN. Any comparison involving NULL produces UNKNOWN, which behaves like FALSE in a WHERE clause — those rows are excluded. The only way to check for NULL is IS NULL or IS NOT NULL.

For senior analysts

COALESCE(column, default) substitutes a value for NULL. COALESCE(delivery_date, '9999-12-31') lets you sort undelivered orders to the bottom without breaking the sort logic. NULLIF(value, 0) returns NULL when the value equals 0 — useful to prevent division-by-zero: SUM(revenue) / NULLIF(COUNT(orders), 0).

---

A Real Business Question

"Show me all pending orders placed in January 2024 with a gross total over £50."

SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.quantity * o.unit_price   AS gross_total,
    o.status
FROM orders o
WHERE o.status = 'pending'
  AND o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND o.quantity * o.unit_price > 50
ORDER BY o.quantity * o.unit_price DESC;

Sample output:

order_id	customer_id	order_date	gross_total	status
O008	C002	2024-01-18	389.00	pending
O007	C003	2024-01-15	249.95	pending
O003	C002	2024-01-09	149.97	pending

---

Practice Exercises

Warm-up 1. Retrieve all orders where status = 'completed'. 2. Find customers in city = 'Mumbai'. 3. Find orders where the gross total (quantity * unit_price) is greater than £200.

Main 4. Find all orders placed in Q4 2023 (October–December) with a gross total between £100 and £500. 5. Find customers whose email ends in @company.com OR who are from country = 'India'. Use parentheses correctly. 6. Find products in category IN ('Electronics', 'Furniture') with unit_price > 100. 7. Find undelivered orders (delivery_date IS NULL) that are neither cancelled nor refunded. Sort by order_date ascending.

Stretch 8. Customers whose first_name starts with 'R' and who signed up in 2022 or 2023. 9. Orders where discount_pct IS NOT NULL and the discount is greater than 10%. Calculate the gross and net total for each. 10. Explain in plain English what this returns — then verify with your data:

SELECT * FROM orders
WHERE NOT (status = 'cancelled' OR quantity * unit_price < 10);

---

Interview Questions

[Beginner] What is the difference between WHERE status = NULL and WHERE status IS NULL?

Show answer

WHERE status = NULL never returns any rows because NULL = NULL evaluates to UNKNOWN (not TRUE) in SQL. Any comparison involving NULL produces UNKNOWN, which filters out the row.

WHERE status IS NULL is the correct syntax — it specifically checks whether the value is NULL.

-- Always returns zero rows, regardless of data
WHERE status = NULL;

-- Returns rows where status has no value
WHERE status IS NULL;

This is one of the most common beginner mistakes in SQL. The rule: never use = with NULL. Always use IS NULL or IS NOT NULL.

---

[Beginner] What does LIKE '%2024%' match?

Show answer

It matches any string that contains the substring 2024 anywhere in the value. The % wildcard matches any sequence of zero or more characters.

Examples of strings it would match: - 'order-2024-01' — contains 2024 - '2024-annual-report' — starts with 2024 - 'report_2024' — ends with 2024 - 'q1-2024-results-v2' — contains 2024 in the middle

It would NOT match '202' or '24-01' — the substring 2024 must be present exactly.

Note: leading % prevents index use. On large tables, this causes a full table scan.

---

[Mid-level] Why is NOT IN dangerous when the list might contain NULL values?

Show answer

NOT IN (a, b, NULL) expands internally to: column <> a AND column <> b AND column <> NULL.

The third condition column <> NULL always evaluates to UNKNOWN. Since WHERE requires TRUE (not UNKNOWN) to include a row, and UNKNOWN AND anything = UNKNOWN, the entire condition returns UNKNOWN for every row — so zero rows are returned.

This is especially dangerous with subqueries:

-- If ANY row in the subquery has a NULL product_id, this returns ZERO rows
WHERE product_id NOT IN (SELECT product_id FROM discontinued_products);

-- Safe: filter NULLs out of the subquery
WHERE product_id NOT IN (
    SELECT product_id FROM discontinued_products WHERE product_id IS NOT NULL
);

-- Best: use NOT EXISTS — correctly handles NULLs
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_products dp
    WHERE dp.product_id = orders.product_id
);

---

[Mid-level] Write two equivalent queries to filter Q1 2024 orders — one using BETWEEN, one using comparison operators.

Show answer

-- Using BETWEEN (inclusive on both ends)
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- Using comparison operators
SELECT order_id, order_date
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <= '2024-03-31';

Both return identical results. BETWEEN is slightly more readable for range filters. The comparison approach gives more control — useful when you need exclusive boundaries or when the column is a datetime and you want < '2024-04-01' instead of <= '2024-03-31 23:59:59'.

---

[Mid-level] A stakeholder asks for "all orders that are high-value (gross total over £1000) OR from VIP customers in London." Write the WHERE clause and explain why parentheses matter.

Show answer

-- CORRECT: parentheses make the OR explicit
WHERE quantity * unit_price > 1000
   OR (c.segment = 'VIP' AND c.city = 'London')

Without the parentheses around the second condition:

-- WRONG: AND binds before OR, so this means:
-- (order_total > 1000 OR segment = 'VIP') AND city = 'London'
WHERE quantity * unit_price > 1000 OR c.segment = 'VIP' AND c.city = 'London'

The wrong version restricts high-value orders to London only — not what was asked. The AND/OR precedence rule: AND always binds tighter than OR. Parenthesise every OR condition to make intent explicit and prevent silent bugs.

---

[Senior] A query with WHERE email LIKE '%@gmail.com' on a 50-million-row table takes 30 seconds. How would you diagnose and fix it?

Show answer

Diagnosis: Run EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL). A leading wildcard ('%...') forces a full table scan — no B-tree index can help because the database doesn't know which prefix to look for. You'll see "Full Table Scan" or "Seq Scan" in the plan.

Fix options (ranked by effectiveness):

1. Store the email domain separately — add a email_domain VARCHAR(100) column, populate it with SUBSTRING_INDEX(email, '@', -1), index it, and query WHERE email_domain = 'gmail.com'. This is the cleanest long-term solution.

2. Full-text search index:

3. MySQL: ALTER TABLE customers ADD FULLTEXT INDEX (email); SELECT ... WHERE MATCH(email) AGAINST ('gmail.com')

4. PostgreSQL: CREATE INDEX ON customers USING GIN (to_tsvector('english', email));

5. Reverse the string — store email_reversed as REVERSE(email) and index it. Query WHERE email_reversed LIKE 'moc.liamg@%'. The wildcard is now trailing, so the index works.

6. Accept the cost — if this query runs in a nightly batch job (not in real-time), a 30-second scan may be acceptable. Only optimise based on actual usage patterns.

---

Previous: 01-select-statement | Next: 03-order-by