WITH: giving a subquery a name you can reuse

A subquery you've already written, without a name

SQL: grouping & aggregates used GROUP BY with HAVING to keep only the groups above a threshold, such as courses with more than one enrollment. Written as a subquery, that result is computed once and thrown away as soon as the outer query finishes. Postgres can compute the same subquery and give it a name: a Common Table Expression, WITH name AS (subquery) SELECT ... FROM name.

WITH popular_courses AS (
  SELECT course_id FROM enrollments GROUP BY course_id HAVING count(*) > 1
)
SELECT * FROM courses JOIN popular_courses ON popular_courses.course_id = courses.id

popular_courses, the name after WITH, exists only while this one statement runs. It isn't a table or a saved view, and it's gone when the query finishes. Against this app's enrollments and courses tables, the query returns every course with more than one enrollment.

Why a name changes anything

A subquery written inline, (SELECT course_id FROM enrollments GROUP BY course_id HAVING count(*) > 1), has to be written out in full everywhere it's needed: twice in a query that needs it in a JOIN and a WHERE, three times if it's needed in three places. WITH writes it once, under a name, and every later reference is just that name: one source, reused instead of copied.

basics, on purpose

CTEs can do more than this course covers. A CTE can reference itself (WITH RECURSIVE), for tree- and graph-shaped data such as an org chart or a comment thread's replies. And you can control materialization, which is lesson 6's subject. This course sticks to the non-recursive half: one CTE or several, none referencing itself, which is the shape you'll reach for first.

What this course covers

Seven lessons, all run against this app's database. This lesson shows the SQL a CTE produces. Lesson 2 covers ActiveRecord::Relation#with, Rails' method for building one, checked against its installed doc comments. Lesson 3 shows a gotcha: defining a CTE and never using it. Lesson 4 joins against a CTE built from grouped data in this app's tables, and lesson 5 builds one CTE from another. Lesson 6 checks the most widespread, and most often wrong, piece of CTE folklore with EXPLAIN.