SQL WHERE Clause

The WHERE clause filters rows before they’re returned by a query. Only rows where the condition evaluates to TRUE are included. This is true for SELECT, UPDATE, DELETE, and any other statement that reads from a table.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Comparison Operators

-- Equal
WHERE status = 'active'

-- Not equal
WHERE status != 'cancelled'
-- OR (same meaning)
WHERE status <> 'cancelled'

-- Greater than / less than
WHERE price > 50
WHERE price >= 50
WHERE price < 100
WHERE price <= 99.99

-- Range (inclusive on both ends)
WHERE price BETWEEN 10 AND 50
-- Equivalent to: WHERE price >= 10 AND price <= 50

-- NOT BETWEEN
WHERE price NOT BETWEEN 10 AND 50

AND, OR, NOT

Combine multiple conditions using logical operators:

-- Both conditions must be true
SELECT * FROM products
WHERE category = 'electronics' AND price < 200;

-- Either condition can be true
SELECT * FROM customers
WHERE city = 'London' OR city = 'Manchester';

-- Negate a condition
SELECT * FROM orders
WHERE NOT status = 'cancelled';
-- More readable as: WHERE status != 'cancelled'

Operator precedence: NOT is evaluated first, then AND, then OR. Use parentheses when combining AND and OR to be explicit:

-- Ambiguous without parentheses
WHERE category = 'books' OR category = 'music' AND price > 20

-- This is what the database actually evaluates (AND first):
WHERE category = 'books' OR (category = 'music' AND price > 20)

-- If you meant something else, add parentheses:
WHERE (category = 'books' OR category = 'music') AND price > 20

Always add parentheses when mixing AND and OR — it makes the intent clear and prevents bugs.

IN: Matching a List of Values

IN is a clean way to test membership in a list, without chaining multiple OR conditions:

-- Instead of:
WHERE status = 'pending' OR status = 'active' OR status = 'processing'

-- Use:
WHERE status IN ('pending', 'active', 'processing')

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

IN also accepts a subquery:

-- Orders from customers in London
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE city = 'London'
)

Watch out: NOT IN with a subquery fails silently if the subquery returns any NULL values. Use NOT EXISTS instead in those cases:

-- Safer than NOT IN when subquery might return NULLs
WHERE NOT EXISTS (
    SELECT 1 FROM blacklisted_emails b WHERE b.email = users.email
)

LIKE: Pattern Matching

LIKE matches string patterns using two wildcards:

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

-- Starts with 'J'
WHERE name LIKE 'J%'

-- Ends with '.com'
WHERE email LIKE '%.com'

-- Contains 'smith' anywhere
WHERE name LIKE '%smith%'

-- Exactly 5 characters
WHERE code LIKE '_____'

-- Second character is 'a'
WHERE product_name LIKE '_a%'

-- Negate the pattern
WHERE email NOT LIKE '%@gmail.com'

Performance note: % at the start of a pattern (LIKE '%smith%') prevents index usage — the database must scan every row. % at the end only (LIKE 'smith%') can use a B-tree index efficiently.

Case sensitivity: LIKE is case-sensitive in PostgreSQL and case-insensitive in MySQL. Use ILIKE in PostgreSQL for case-insensitive matching:

WHERE name ILIKE '%smith%'  -- PostgreSQL only
WHERE LOWER(name) LIKE '%smith%'  -- works everywhere

NULL Handling

NULL is not a value — it represents the absence of a value. Standard comparison operators don’t work with NULL:

-- This returns NO rows, even if phone IS NULL
WHERE phone = NULL    -- wrong

-- This is the correct syntax
WHERE phone IS NULL

-- Find rows where phone exists (is not null)
WHERE phone IS NOT NULL

NULL compared to anything — including another NULL — returns NULL (neither TRUE nor FALSE). This is why WHERE column = NULL always returns no rows.

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

-- Find customers who have provided all contact info
SELECT name FROM customers WHERE phone IS NOT NULL AND email IS NOT NULL;

WHERE with Dates

Date comparisons work like numeric comparisons — the string literal is implicitly cast to a date:

-- Exact date
WHERE order_date = '2025-06-01'

-- Range
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'

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

-- Specific year (non-sargable: wrapping in a function blocks index use)
WHERE YEAR(order_date) = 2025   -- bad: can't use index

-- Better: range comparison (sargable: can use index)
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation:

-- WHERE: filter before aggregation
SELECT category, SUM(amount)
FROM orders
WHERE status = 'completed'       -- filter rows first
GROUP BY category
HAVING SUM(amount) > 10000;      -- then filter groups

You cannot use aggregate functions (SUM, COUNT, etc.) in a WHERE clause — that’s what HAVING is for.

Subqueries in WHERE

-- Products that have at least one order
SELECT product_id, name
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id FROM order_items
);

-- Or equivalently with EXISTS (often faster for large datasets):
SELECT p.product_id, p.name
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

Practical Examples

Find overdue invoices:

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

Find products low on stock:

SELECT name, sku, stock_quantity
FROM products
WHERE is_active = TRUE
  AND stock_quantity BETWEEN 1 AND 10
ORDER BY stock_quantity;

Find users who haven’t logged in recently:

SELECT user_id, email, last_login_at
FROM users
WHERE last_login_at < NOW() - INTERVAL '90 days'
   OR last_login_at IS NULL;