What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped, so it cannot reference aggregate functions like SUM or COUNT. HAVING filters groups after GROUP BY has run, so it can. As a rule: filter rows with WHERE, filter groups with HAVING.

Why does WHERE column = NULL return no rows?

Any comparison with NULL evaluates to "unknown" rather than true, and WHERE keeps only rows where the condition is true. To test for NULL you must use IS NULL or IS NOT NULL, which return a clean true or false.

What is the difference between LIKE and ILIKE?

LIKE matches text case-sensitively, while ILIKE (PostgreSQL-specific) matches case-insensitively. Both use % to match any number of characters and _ to match a single character.

Beginner

SQL WHERE Clause: Filtering Rows with AND, OR, IN & LIKE

The WHERE clause is how you ask a question of your data. Learn comparison operators, AND/OR logic, IN, BETWEEN, LIKE, and the NULL traps that quietly drop rows.

WHERE is the clause that turns "give me every row" into "give me the rows I actually care about". It filters a result set by testing a condition against each row and keeping only the rows where that condition is true. Almost every useful query has one. Keep the SQL cheat sheet open while you read.

What WHERE Does

WHERE runs after FROM and before GROUP BY. For each row the database evaluates your condition and keeps the row only if the result is true — not false, and not unknown. That last distinction matters once NULLs are involved, which is covered below.

-- Only orders worth more than 100
SELECT id, total
FROM orders
WHERE total > 100;

Comparison Operators

The building blocks of any filter are the comparison operators: = (equal), <> or != (not equal), and the four inequalities < <= > >=. They work on numbers, text, and dates alike.

SELECT * FROM orders WHERE status = 'paid';      -- equal
SELECT * FROM orders WHERE status <> 'refunded';  -- not equal
SELECT * FROM orders WHERE total >= 500;          -- at least 500
SELECT * FROM users WHERE created_at < '2026-01-01'; -- before a date

AND, OR & NOT

Combine conditions with AND (both must be true), OR (either can be true), and NOT (negation). When you mix AND and OR, wrap the OR group in parentheses — AND binds tighter than OR, and forgetting that is one of the most common filtering bugs.

-- Paid orders over 100
SELECT * FROM orders
WHERE status = 'paid' AND total > 100;

-- Parentheses change the meaning entirely
SELECT * FROM orders
WHERE (status = 'paid' OR status = 'pending')
  AND total > 100;

IN & BETWEEN

IN checks whether a value matches any item in a list — much cleaner than a chain of OR conditions. BETWEEN tests an inclusive range on both ends.

-- These two are equivalent
SELECT * FROM orders WHERE status IN ('paid', 'pending', 'shipped');
SELECT * FROM orders
WHERE status = 'paid' OR status = 'pending' OR status = 'shipped';

-- BETWEEN is inclusive of both bounds
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

LIKE & Pattern Matching

LIKE matches text against a pattern. The % wildcard matches any number of characters and _ matches exactly one. Use ILIKE in PostgreSQL for a case-insensitive match.

SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- ends with
SELECT * FROM users WHERE name LIKE 'A%';            -- starts with A
SELECT * FROM users WHERE name ILIKE '%smith%';      -- contains, any case

NULL in WHERE

Any comparison with NULL returns "unknown", never true — so WHERE silently drops those rows. To test for missing values you must use IS NULL and IS NOT NULL, never = NULL.

-- Wrong: returns nothing, because = NULL is never true
SELECT * FROM users WHERE phone = NULL;

-- Right
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

Common Mistakes & Tips

Mixing AND and OR without parentheses — AND binds tighter, which changes the result
Using = NULL instead of IS NULL — comparisons with NULL are never true
Wrapping the filtered column in a function (WHERE LOWER(email) = ...) — it blocks index use
Expecting BETWEEN to cover a full day for timestamps — it stops at midnight
Trying to filter an aggregate in WHERE — use HAVING after GROUP BY instead

WHERE is the workhorse of SQL — you will write it in nearly every query. Practice filtering on real e-commerce and banking tables to make the operators second nature.