---

🟢 Junior Level

Both WHERE and HAVING are SQL clauses used to filter data, but they operate at fundamentally different stages of the query execution pipeline:

• WHERE: Filters individual rows before any grouping or aggregation takes place.
• HAVING: Filters aggregated groups after GROUP BY has collapsed rows into summary buckets.

Simple Analogy

Imagine sorting a large collection of coins:

1. WHERE (Pre-Filter): You inspect the pile and remove all foreign currency or counterfeit tokens before doing anything else.
2. GROUP BY (Bucketing): You stack the remaining coins into piles by denomination (1¢, 5¢, 10¢, 25¢).
3. HAVING (Post-Filter): You inspect the finished stacks and discard any pile containing fewer than 10 coins.

-- WHERE: Filters individual orders by status before grouping
-- HAVING: Filters resulting customer groups where completed order count exceeds 5
SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'COMPLETED'  -- 1. Filters rows before aggregation (Uses Indexes!)
GROUP BY customer_id
HAVING COUNT(*) > 5;        -- 2. Filters groups after aggregation

When to Use Which:

• Use WHERE to eliminate irrelevant rows as early as possible using table columns.
• Use HAVING only when filtering on calculated aggregate functions (COUNT, SUM, AVG, MIN, MAX).

---

🟡 Middle Level

The Logical SQL Execution Pipeline

In relational database architecture, SQL queries execute in a strict logical order that differs from written syntax:

\[\text{1. FROM / JOIN} \longrightarrow \text{2. WHERE} \longrightarrow \text{3. GROUP BY} \longrightarrow \text{4. HAVING} \longrightarrow \text{5. SELECT} \longrightarrow \text{6. ORDER BY} \longrightarrow \text{7. LIMIT}\]

Because WHERE runs at Step 2 and HAVING runs at Step 4:

• WHERE can utilize table B-tree indexes, drastically reducing the volume of data fed into sorting and aggregation nodes.
• HAVING cannot use regular B-tree indexes on aggregate calculations (COUNT, SUM, AVG), as these values are computed on the fly.

Performance Impact: The Misplaced Filter Anti-Pattern

A common mistake is placing non-aggregate column filters in HAVING instead of WHERE:

-- ❌ CATASTROPHIC ANTI-PATTERN: Aggregates the ENTIRE table before filtering!
SELECT city, COUNT(*) 
FROM users
GROUP BY city
HAVING city = 'Chicago';  -- Forces HashAggregate across 50,000,000 global users!

-- ✅ OPTIMAL PATTERN: Filters rows BEFORE aggregation begins
SELECT city, COUNT(*) 
FROM users
WHERE city = 'Chicago'    -- Index Scan isolates Chicago rows immediately (~50k rows)
GROUP BY city;

Feature Comparison Matrix

Feature / Behavior	WHERE Clause	HAVING Clause
Filter Target	Individual raw table/join rows	Collapsed summary groups
Pipeline Stage	Evaluated before GROUP BY	Evaluated after GROUP BY
Aggregate Functions	❌ Forbidden (WHERE COUNT(*) > 1 triggers syntax error)	✅ Allowed (HAVING COUNT(*) > 1)
B-Tree Index Usage	✅ Full index support (Index Scan, Bitmap Scan)	❌ Cannot index dynamic aggregate expressions
Memory Pressure	Lowers memory by discarding rows before hashing	Does not prevent GROUP BY memory bloat

---

🔴 Senior Level

Automatic Filter Push-down & Its Limitations

In simple queries, PostgreSQL’s query rewriter attempts Filter Push-down, automatically moving non-aggregate predicates from HAVING down into WHERE:

-- Written Query:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (1, 2, 3) AND AVG(salary) > 80000;

-- Planner's Internal Push-Down Rewrite:
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (1, 2, 3)  -- PUSHED DOWN to enable Index Scan!
GROUP BY department_id
HAVING AVG(salary) > 80000;

Why You Must NEVER Rely on Automatic Push-down:

Filter Push-down is an optimizer heuristic that easily breaks when queries involve:

1. Multi-level database Views.
2. Non-inlined CTEs (WITH ... AS MATERIALIZED).
3. Set operations (UNION, INTERSECT).
4. Complex WINDOW function pipelines.

Always place non-aggregate conditions in WHERE explicitly in production code.

Memory Pressure: HashAggregate Spills to Disk

When non-aggregate filters are misplaced in HAVING, the HashAggregate executor must allocate hash buckets in memory for every distinct grouping key across the entire table. If the number of distinct groups exceeds work_mem, PostgreSQL spills partition batches to disk temporary files:

HashAggregate (cost=... Memory Usage: 4096kB, Disk Spill: 450MB)  <-- DISK SPILL!

Moving the filter to WHERE drops the active working set by 99%, keeping the entire hash table in RAM.

Modern SQL: The Aggregate FILTER Clause

PostgreSQL supports the standard ANSI SQL FILTER (WHERE ...) clause, allowing fine-grained conditional aggregation within the same grouping:

SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (WHERE salary > 100000) AS high_earners_count,
    AVG(salary) FILTER (WHERE status = 'ACTIVE') AS active_avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) FILTER (WHERE salary > 100000) >= 5;

---

4 Tricky Questions

1. Can you use a HAVING clause without a GROUP BY clause? What happens if the condition is not met?

Answer:
Yes, this is fully valid ANSI SQL.
When HAVING is used without GROUP BY, the entire table is treated as a single implicit group.

• If the condition is met, the query returns 1 row with the aggregate result:

  SELECT AVG(salary) FROM employees HAVING COUNT(*) > 10;
  -- If there are 50 employees, returns 1 row with average salary.
  

• If the condition is not met (e.g., there are only 5 employees), the query returns 0 rows (an empty set)!
  Crucial nuance: Without HAVING, SELECT AVG(salary) FROM employees on an empty table returns 1 row containing NULL. Adding HAVING causes it to return 0 rows.

2. Why does writing WHERE COUNT(*) > 5 trigger a syntax error at compile time?

Answer:
Because of the Logical Query Processing Order.
The WHERE clause executes at Step 2 of the pipeline, whereas grouping and aggregation occur at Step 3 (GROUP BY).
When the database processes WHERE, individual rows are being evaluated one at a time. The concept of a “group” and aggregate metrics like COUNT(*), SUM(), or AVG() do not yet exist in memory. The SQL parser rejects aggregate functions in WHERE to enforce this relational boundary.

3. Why can’t Window Functions (e.g., ROW_NUMBER()) be filtered in either WHERE or HAVING?

Answer:
Because Window Functions are evaluated during the SELECT phase (Step 5) of the logical query pipeline, which occurs after both WHERE (Step 2) and HAVING (Step 4).
At the time WHERE and HAVING are evaluated, window partition framing and row numbering have not yet been computed. To filter on a window function result, you must wrap the query in a Common Table Expression (CTE) or a Subquery so that the outer query’s WHERE clause executes on the completed window output:

WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn
    FROM items
)
SELECT * FROM ranked WHERE rn <= 3;

4. What is the difference between HAVING COUNT(CASE WHEN status = 'FAILED' THEN 1 END) > 5 and HAVING COUNT(*) FILTER (WHERE status = 'FAILED') > 5?

Answer:
Both expressions produce identical logical results, but the FILTER (WHERE ...) syntax (ISO SQL:2003 standard, supported in PostgreSQL) is superior:

1. Readability: Clean, explicit intent without relying on CASE statement fallthrough semantics.
2. Performance: The engine optimizes the FILTER clause directly in the aggregate accumulator without evaluating boolean CASE branches or generating intermediate values.

---

🎯 Interview Cheat Sheet

Core Differences

• WHERE: Filters rows before aggregation; runs at Step 2; utilizes B-tree indexes; aggregates are forbidden.
• HAVING: Filters groups after aggregation; runs at Step 4; aggregates (COUNT, SUM, AVG) are allowed; cannot use indexes on dynamic aggregates.
• Logical Pipeline: FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT $\rightarrow$ ORDER BY $\rightarrow$ LIMIT.

Key Optimizer Insights

• Filter Push-Down: PostgreSQL pushes non-aggregate filters from HAVING down to WHERE, but complex views or non-inlined CTEs break push-down. Always write non-aggregates in WHERE explicitly.
• HAVING Without GROUP BY: Treats the whole table as 1 group. Returns 0 rows if the condition fails (unlike standard aggregation which returns 1 row with NULL).
• Window Functions: Evaluated in SELECT (Step 5); cannot appear in WHERE or HAVING. Requires a CTE or subquery.

Red Flags (What NOT to Say)

• ❌ “HAVING is just another syntax for WHERE.” (They execute at completely different stages of query execution).
• ❌ “Postgres will always automatically push misplaced HAVING filters down into WHERE.” (Push-down fails across complex views, window pipelines, and non-inlined CTEs).
• ❌ “You can filter by ROW_NUMBER() in the HAVING clause.” (Window functions execute after HAVING; requires a CTE/subquery).

Related Topics

• What does GROUP BY do — HashAggregate vs GroupAggregate internals
• When to use HAVING — Practical business query patterns
• What are Window Functions — Window framing vs group aggregation