MEPX
Chapter 8 of 10All chapters
  1. 1What SQL is
  2. 2SELECT and WHERE
  3. 3NULL
  4. 4Sorting and limiting
  5. 5Aggregates and GROUP BY
  6. 6Joins
  7. 7Keys and relationships
  8. 8Subqueries and CTEs
  9. 9Indexes and performance
  10. 10Changing data

Chapter 8 of 10

Subqueries and CTEs

Queries inside queries.

Two ways to nest

A subquery sits inside another statement, in a WHERE, a FROM or a SELECT. A common table expression names it up front with WITH, which reads far better once there is more than one.

  • WITH recent AS (...) SELECT ... FROM recent puts the steps in reading order.
  • A correlated subquery runs per row and is often the slow part of a query.

EXISTS

EXISTS asks whether a subquery returns anything at all and stops at the first hit. It is usually clearer and faster than counting rows just to compare against zero.