HAVING | Engineering Interview Kit

Concept

You learned that the WHERE clause filters rows.
But what if you want to filter based on an aggregated sum?
For example: “Show me all departments where the total payroll is greater than $1,000,000”.

If you try to write:

SELECT department, SUM(salary) 
FROM employees 
WHERE SUM(salary) > 1000000  -- CRASH!
GROUP BY department;

This will immediately throw a syntax error.

Why? The Order of Execution:
The database executes the WHERE clause before the GROUP BY clause. The database tries to evaluate WHERE SUM(salary) > 1M, but the rows haven’t been put into buckets yet, so the sum doesn’t mathematically exist yet.

The HAVING clause is the exact same thing as the WHERE clause, but it executes after the GROUP BY phase.

Mental Model: The 5-Step Execution Order

To understand HAVING, you must memorize the SQL execution order:

FROM: Load the table.
WHERE: Filter the raw, individual rows.
GROUP BY: Smash the remaining rows into buckets.
HAVING: Filter the newly created buckets.
SELECT: Format the final output columns.

How It Works

SELECT department, SUM(salary) AS total_payroll
FROM employees
-- 1. Filter out part-time workers before bucketing
WHERE employment_type = 'Full-Time' 
GROUP BY department
-- 2. Filter the buckets based on the aggregate math
HAVING SUM(salary) > 1000000;

WHERE vs HAVING

Many developers lazily use HAVING when they should use WHERE. This causes catastrophic performance issues.

BAD CODE:

SELECT department, COUNT(id)
FROM employees
GROUP BY department
HAVING department = 'Engineering';

Why is this bad? The database just spent massive CPU cycles processing the entire company, creating 50 different department buckets, calculating the count for all 50, and only at the very end did it throw 49 of them in the garbage.

GOOD CODE:

SELECT department, COUNT(id)
FROM employees
WHERE department = 'Engineering'
GROUP BY department;

Why is this good? The WHERE clause uses an index to instantly grab only the Engineering rows. It creates exactly 1 bucket and does exactly 1 math calculation. It is 1,000x faster.

The Rule: If a condition does not involve an aggregate function (like SUM or COUNT), it absolutely must go in the WHERE clause.

Interview Questions

Q: Look at this query: SELECT department, SUM(salary) AS total FROM employees GROUP BY department HAVING total > 10000. Will this execute successfully in standard SQL (like PostgreSQL)?
A: No, it will throw an error: column "total" does not exist.
This is because of the order of execution. The HAVING clause is executed before the SELECT clause. The alias total is defined in the SELECT clause. Therefore, when the database is processing the HAVING clause, the alias hasn’t been created yet. You must write the full function: HAVING SUM(salary) > 10000.
(Note: MySQL famously breaks the SQL standard and allows you to use aliases in the HAVING clause for convenience, but PostgreSQL and SQL Server strictly forbid it).

Q: Can you use a HAVING clause without a GROUP BY clause?
A: Yes, but it is extremely rare and effectively turns the entire table into one single implicit group. For example: SELECT SUM(salary) FROM employees HAVING SUM(salary) > 100. If the total sum is 150, it returns the row. If the total sum is 50, it returns exactly 0 rows. In modern practice, you should never do this as it is confusing to read.