What is the difference between WHERE and HAVING in SQL?

WHAT THIS TESTS: This question tests whether you understand the logical processing order of a SQL query and the distinction between row-level and group-level filtering. Interviewers want to see that you know WHERE is evaluated before GROUP BY and aggregation, while HAVING is evaluated after. They also want to see if you can articulate why aggregate functions are illegal in WHERE in standard SQL.

A GOOD ANSWER COVERS: First, state the execution order clearly: FROM and WHERE happen first, then GROUP BY and aggregation, then HAVING, then SELECT and ORDER BY. Second, explain that WHERE filters individual rows based on column values and cannot see aggregate results because grouping has not happened yet. Third, explain that HAVING filters the grouped results and is the only place in standard SQL where you can reference aggregate functions like COUNT, SUM, AVG, MAX, or MIN directly in a predicate. Fourth, give a concrete example where you group by department and filter on an aggregate such as COUNT(employee_id) greater than ten, noting that moving that predicate to WHERE would raise a syntax error or produce wrong results.

COMMON WRONG ANSWERS: A red flag is saying HAVING is just WHERE for grouped queries without mentioning aggregates or execution order. Another red flag is claiming HAVING is faster or slower as a blanket statement; performance depends on indexes and the optimizer, not the clause itself. Some candidates incorrectly say you can use WHERE with aggregate functions by repeating the aggregate expression; this fails because the aggregate does not exist at the WHERE stage. Also avoid saying HAVING can only be used with GROUP BY; while it is rare without it, standard SQL allows HAVING on the whole table as a single implicit group.

LIKELY FOLLOW-UPS: The interviewer may ask whether you can reference column aliases in HAVING or WHERE, which tests deeper knowledge of logical processing: aliases defined in SELECT are generally unavailable in WHERE and HAVING in most databases, though some engines like MySQL allow it in HAVING. They might ask about window functions and whether you can filter on them in HAVING, which usually requires a subquery or CTE because window functions execute after HAVING. They may also ask for a query rewrite using a subquery instead of HAVING to see if you understand equivalent forms.

ONE CONCRETE EXAMPLE: Consider a table named orders with columns order_id, customer_id, and total. To find customers who have placed more than five orders, you write SELECT customer_id, COUNT() as order_count FROM orders GROUP BY customer_id HAVING COUNT() > 5. You cannot write WHERE COUNT(*) > 5 because at the WHERE stage each row is a single order, not a customer group, and the aggregate value does not yet exist. Attempting to put the condition in WHERE would result in a syntax error in standard SQL.