Subqueries and CTEs

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Subqueries and CTEs
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A subquery is a query inside another query and can supply a scalar, a row set, or an existence test. `EXISTS` expresses match/no-match intent without introducing duplicates. CTEs turn a query into named stages for readability, but they are not automatic performance magic.

Connect it to a real scenario

Use `EXISTS` to select published tutorials that have at least one lesson. Then calculate enrollment counts in a CTE and join popular tutorials back to catalog metadata.

Use `EXISTS` for existence checks
Use CTEs as named query stages
Evaluate readability and the execution plan separately

Try the working example

sql

SELECT t.tutorial_id, t.title
FROM app.tutorials t
WHERE t.is_published
  AND EXISTS (
    SELECT 1 FROM app.lessons l
    WHERE l.tutorial_id = t.tutorial_id
  );

WITH enrollment_totals AS (
  SELECT tutorial_id, count(*) AS enrollments
  FROM app.enrollments
  GROUP BY tutorial_id
)
SELECT t.title, e.enrollments
FROM enrollment_totals e
JOIN app.tutorials t USING (tutorial_id)
WHERE e.enrollments >= 10;

You should see

You obtain lists of tutorials with lessons and tutorials meeting the popularity threshold.

5-minute try-it

Use `NOT EXISTS` to find enrollments that do not yet have progress rows.

One important caution

`NOT IN` can surprise you when its subquery returns NULL. Prefer `NOT EXISTS` for many anti-join cases.

PostgreSQL — WITH Queries — PostgreSQL Global Development Group

Easy traps

`NOT IN` can surprise you when its subquery returns NULL. Prefer `NOT EXISTS` for many anti-join cases.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Use `NOT EXISTS` to find enrollments that do not yet have progress rows.

You'll know it worked when: You obtain lists of tutorials with lessons and tutorials meeting the popularity threshold.