WHERE vs HAVING mental model in SQL - Visual Side-by-Side Comparison

Concept Flow - WHERE vs HAVING mental model

Start with Table Data

↓

Apply WHERE filter

↓

Group Data (if GROUP BY used)

↓

Apply HAVING filter on groups

↓

Return final result

Data is first filtered row-by-row using WHERE, then grouped, and groups are filtered using HAVING before final output.

Execution Sample

SQL

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 3000
GROUP BY department
HAVING COUNT(*) > 2;

This query filters employees with salary > 3000, groups them by department, then shows only departments with more than 2 such employees.

Execution Table

Step	Action	Data State	Filter Applied	Resulting Rows/Groups
1	Start with all employees	All rows from employees table	None	All employee rows
2	Apply WHERE salary > 3000	Rows with salary > 3000	Row-level filter	Filtered employee rows
3	Group by department	Grouped filtered rows	Grouping	Groups of employees per department
4	Apply HAVING COUNT(*) > 2	Groups with count > 2	Group-level filter	Departments with more than 2 employees
5	Return final result	Filtered groups	None	Final output rows

💡 No more steps; query returns groups filtered by HAVING after WHERE filtering and grouping.

Variable Tracker

Variable	Start	After WHERE	After GROUP BY	After HAVING	Final
Rows	All employees	Employees with salary > 3000	Employees grouped by department	Groups with count > 2	Final filtered groups
Count	N/A	N/A	Count per group	Count > 2 filter applied	Count per returned group

Key Moments - 3 Insights

Why can't we use aggregate functions like COUNT() in WHERE?

WHERE filters rows before grouping, so aggregate functions which need groups are not available yet (see execution_table step 2 vs 4).

Can HAVING be used without GROUP BY?

Yes, HAVING can filter on aggregate results even without GROUP BY, but it filters on the whole result treated as one group (see execution_table step 4).

Does WHERE filter groups or individual rows?

WHERE filters individual rows before grouping, so it affects which rows enter groups (see execution_table step 2).

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the row-level filter applied?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Check the 'Filter Applied' column for 'Row-level filter' in execution_table.

According to variable_tracker, what happens to 'Rows' after GROUP BY?

A Rows are grouped by department

B Rows are counted

C Rows are filtered by salary

D Rows are returned as final output

💡 Hint

Look at the 'Rows' variable state under 'After GROUP BY' in variable_tracker.

If we remove the WHERE clause, how does the execution_table change at step 2?

A Step 2 applies HAVING filter

B Step 2 groups rows

C Step 2 applies no filter and keeps all rows

D Step 2 returns final result

💡 Hint

Refer to execution_table step 2 description about WHERE filtering.

Concept Snapshot

WHERE filters rows before grouping.
HAVING filters groups after grouping.
WHERE cannot use aggregates like COUNT().
HAVING can filter on aggregates.
Use WHERE for row-level conditions.
Use HAVING for group-level conditions.

Full Transcript

This visual execution shows how SQL processes WHERE and HAVING clauses. First, the database starts with all rows. Then WHERE filters rows individually before any grouping. Next, rows are grouped by the specified column(s). After grouping, HAVING filters groups based on aggregate conditions like COUNT(). Finally, the filtered groups are returned as the query result. WHERE works on raw rows, so it cannot use aggregate functions. HAVING works on groups, so it can use aggregates. This helps understand when to use WHERE versus HAVING in SQL queries.