Most slow queries come from a few shapes, and each has a standard rewrite that returns the same rows:
- A subquery in the
SELECT list per row ((SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id)) can run once per customer. Join to one grouped query instead.
- Joining big tables, then aggregating multiplies rows before shrinking them. Aggregate first in a CTE, then join the small result.
DISTINCT after a join often hides a join that multiplied rows. If you only need "has any", use EXISTS, which stops at the first match.- Selecting columns you don't use makes every step carry more data, especially in columnar engines that only read the columns you name.
Modern optimizers rewrite some of these for you; the plan here, for example, turns simple correlated subqueries into joins. Writing the efficient shape yourself makes the intent clear and doesn't depend on the optimizer. The exercises check the rows and the shape.