SQL WHERE Clause

WHERE filters rows before they’re returned by a query. Only rows where the condition evaluates to TRUE are included — FALSE and NULL conditions exclude the row.

WHERE works with SELECT, UPDATE, and DELETE.

Basic Syntax

SELECT columns
FROM table_name
WHERE condition;

Comparison Operators

WHERE price = 149.99          -- equal to
WHERE price != 149.99         -- not equal (also: <>)
WHERE price > 100             -- greater than
WHERE price >= 100            -- greater than or equal
WHERE price < 500             -- less than
WHERE price <= 499.99         -- less than or equal

WHERE status = 'active'       -- string comparison (case-sensitive in PostgreSQL)
WHERE name != 'archived'

AND, OR, NOT

-- All conditions must be true
WHERE category = 'electronics' AND price < 300 AND is_active = TRUE

-- Either condition can be true
WHERE city = 'London' OR city = 'Manchester'

-- Negate a condition
WHERE NOT is_deleted

-- Parentheses for clarity (critical when mixing AND and OR)
WHERE (category = 'books' OR category = 'music') AND price < 20

Operator precedence: NOT evaluates first, then AND, then OR. Always use parentheses when combining AND and OR — ambiguous expressions cause bugs.

BETWEEN

Inclusive range test (equivalent to >= AND <=):

WHERE price BETWEEN 50 AND 200
-- Same as: WHERE price >= 50 AND price <= 200

WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'

WHERE NOT price BETWEEN 50 AND 200  -- outside the range

IN: Match a List

WHERE status IN ('pending', 'processing', 'shipped')
-- Same as: WHERE status = 'pending' OR status = 'processing' OR status = 'shipped'

WHERE country NOT IN ('US', 'CA', 'MX')

-- IN with subquery
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE country = 'UK'
)

NOT IN with NULLs: If the list contains any NULLs, NOT IN returns no rows. Use NOT EXISTS instead when the subquery might return NULLs.

LIKE: Pattern Matching

WHERE name LIKE 'A%'          -- starts with 'A'
WHERE email LIKE '%.com'      -- ends with '.com'
WHERE description LIKE '%SQL%' -- contains 'SQL'
WHERE code LIKE '___-__'      -- exact pattern: 3 chars, dash, 2 chars

WHERE name NOT LIKE 'test%'   -- does not start with 'test'

-- PostgreSQL: case-insensitive LIKE
WHERE name ILIKE '%smith%'

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

Performance: LIKE '%text%' (leading wildcard) can’t use a B-tree index — full table scan. LIKE 'text%' (trailing wildcard only) can use an index.

NULL Handling

NULL means unknown — not zero, not empty. Standard comparisons never match NULL:

WHERE phone IS NULL           -- correct: find rows with no phone
WHERE phone IS NOT NULL       -- correct: find rows with a phone

WHERE phone = NULL            -- WRONG: always returns no rows
WHERE phone != NULL           -- WRONG: always returns no rows

-- Find customers without a phone number on file
SELECT name, email FROM customers WHERE phone IS NULL;

-- Find orders where the shipping date hasn't been set
SELECT order_id FROM orders WHERE shipped_at IS NULL AND status = 'pending';

WHERE with Date Conditions

WHERE order_date = '2025-06-15'
WHERE order_date >= '2025-01-01'
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'

-- Recent records (PostgreSQL)
WHERE created_at >= NOW() - INTERVAL '30 days'
WHERE created_at >= CURRENT_DATE - 7

-- Year filter (sargable — can use index)
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

-- Non-sargable (avoids index — don't do this)
WHERE YEAR(created_at) = 2025
WHERE DATE(created_at) = '2025-06-15'

Subquery in WHERE

-- Orders from customers in the UK
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE country = 'UK'
)

-- Products that have been ordered (EXISTS is often faster)
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = products.product_id
)

-- Products that have NEVER been ordered
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = products.product_id
)

-- Products priced above the overall average (scalar subquery)
WHERE price > (SELECT AVG(price) FROM products)

WHERE vs HAVING

-- WHERE filters individual rows BEFORE grouping
SELECT category, SUM(amount)
FROM orders
WHERE status = 'completed'       -- filter rows first
GROUP BY category
HAVING SUM(amount) > 10000;      -- filter groups after

You can’t use aggregate functions in WHERE. That’s what HAVING is for.

Practical Examples

Active customers in specific cities:

SELECT name, email, city
FROM customers
WHERE is_active = TRUE
  AND city IN ('London', 'Manchester', 'Birmingham')
ORDER BY city, name;

Overdue invoices:

SELECT invoice_id, customer_id, amount, due_date
FROM invoices
WHERE status = 'unpaid'
  AND due_date < CURRENT_DATE
ORDER BY due_date;

Products with recent sales:

SELECT p.product_id, p.name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.created_at >= NOW() - INTERVAL '90 days'
);