Differentiate between 'WHERE' and 'HAVING' clauses.

Instruction: Explain the difference between the WHERE and HAVING clauses in SQL.

Context: This question evaluates the candidate's understanding of SQL syntax, specifically the conditions for filtering data.

Updated

Official Answer

WHERE filters input rows before grouping. HAVING filters the resulting groups, so it can test an aggregate such as SUM(amount).

This self-contained PostgreSQL example uses three hypothetical sales:

WITH sales(product, amount, status) AS (
  VALUES ('A', 60, 'paid'),
         ('A', 50, 'paid'),
         ('B', 200, 'cancelled')
)
SELECT product, SUM(amount) AS paid_total
FROM sales
WHERE status = 'paid'
GROUP BY product
HAVING SUM(amount) > 100;

The result is one row: A, 110. WHERE removes the cancelled B sale; HAVING keeps A because its paid total exceeds 100. Filtering individual sale amounts instead would answer a different question. These are logical query semantics; the optimizer may choose a different physical execution strategy that preserves the result.

Source

PostgreSQL: table expressions and grouping.

Related Questions