WHERE vs HAVING mental model in SQL - Quick Revision & Key Differences

Recall & Review

beginner

What is the main purpose of the WHERE clause in SQL?

The WHERE clause filters rows before any grouping happens. It decides which rows to include in the query based on conditions.

beginner

What does the HAVING clause do in SQL?

The HAVING clause filters groups after the data has been grouped by GROUP BY. It lets you filter aggregated results.

intermediate

When should you use WHERE instead of HAVING?

Use WHERE to filter individual rows before grouping. Use HAVING to filter groups after aggregation.

intermediate

Can HAVING be used without GROUP BY? Explain.

Yes, HAVING can be used without GROUP BY to filter aggregated results on the whole table, but this is less common.

beginner

Example: Which clause filters rows before aggregation: WHERE or HAVING?

WHERE filters rows before aggregation, so it affects which data is grouped and aggregated.

Which SQL clause filters rows before grouping?

A WHERE

B HAVING

C GROUP BY

D ORDER BY

WHERE filters rows before grouping, HAVING filters after grouping.

Which clause is used to filter groups after aggregation?

A HAVING

B SELECT

C WHERE

D FROM

HAVING filters groups after aggregation, unlike WHERE which filters rows before aggregation.

Can WHERE filter aggregated results directly?

A Yes, always

B Only with HAVING

C Only with GROUP BY

D No, WHERE filters before aggregation

WHERE filters rows before aggregation, so it cannot filter aggregated results directly.

Which clause would you use to filter customers with total sales over 1000?

A WHERE total_sales > 1000

B ORDER BY total_sales > 1000

C HAVING total_sales > 1000

D GROUP BY total_sales > 1000

Filtering on aggregated values like total sales requires HAVING.

What happens if you put an aggregate function in WHERE clause?

A It works fine

B Syntax error or unexpected result

C It filters groups

D It orders rows

Aggregate functions cannot be used in WHERE; they belong in HAVING.

Explain the difference between WHERE and HAVING clauses in SQL.

Think about when filtering happens: before or after grouping.

Describe a real-life example where you would use HAVING instead of WHERE.

Consider filtering on sums or counts, not individual rows.