HAVING vs. Nested Query

“Find all cities with more than two customers living in the city.”

SELECT customer_city, COUNT(*) AS num_customers

FROM customer GROUP BY customer_city

HAVING COUNT(*) > 2;

• Or, can write:

SELECT customer_city, num_customers

FROM (SELECT customer_city, COUNT(*)

FROM customer GROUP BY customer_city)

AS counts (customer_city, num_customers)

WHERE num_customers > 2;

🞑 Grouping and aggregation is computed by inner query

🞑 Outer query selects desired results generated by inner query