WHERE vs HAVING in SQL: A Simple Way to Understand the Difference

WHERE and HAVING are both used to filter data in SQL, but they do so at different stages of the query processing. WHERE filters individual rows before any grouping occurs, while HAVING filters groups after the grouping and calculations are performed.

To illustrate, imagine a scenario where attendees are divided into tables at an event. The bouncer (WHERE) checks each individual at the door to allow entry based on their profile, such as excluding under-18s. Once everyone is seated, the host (HAVING) evaluates each table, merging those with fewer than 4 people. The bouncer cannot assess the table quality since there are no tables at the door, and thus aggregate functions like SUM() and COUNT() cannot be used there.

Using a small orders table, we can demonstrate the difference. In Step 1, WHERE filters rows with an amount greater than 100, returning 4 rows. In Step 2, we calculate the total amount spent by each customer using GROUP BY. Step 3 shows HAVING filtering groups where the total spent exceeds 200, removing Chloe due to her total of 180.

Step 4 demonstrates using both WHERE and HAVING together to get the total money spent per customer, filtering out David due to a cancelled order and Brian's total dropping from 380 to 300 after removing a cancelled order. The crucial point is that SQL processes queries in a specific order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

WHERE evaluates raw rows, while HAVING assesses group totals, explaining the difference in their functionality.