Subqueries and CTEs

After this lesson, you will be able to: Use correlated and non-correlated subqueries, EXISTS, and CTEs (WITH) to express complex queries readably.

Subqueries unlock multi-step questions. CTEs (Common Table Expressions) are the modern, readable version, write your queries in stages instead of nested mess.

Prerequisites:GROUP BY and Aggregations

Non-correlated subqueries

Inner query runs once; result feeds the outer.

sql

-- IN (subquery)
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM posts WHERE published);

-- = (subquery returning one value)
SELECT * FROM posts
WHERE user_id = (SELECT id FROM users WHERE email = '[email protected]');

-- Subquery in FROM (derived table)
SELECT post_count_bucket, COUNT(*) AS users
FROM (
  SELECT user_id, COUNT(*) / 10 AS post_count_bucket
  FROM posts GROUP BY user_id
) t
GROUP BY post_count_bucket;

Correlated subqueries

Inner query references outer row; runs once per outer row.

sql

-- For each user, count their posts (in a subquery)
SELECT u.id, u.name,
  (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count
FROM users u;

-- Often better: JOIN + GROUP BY (faster + cleaner).
-- Use correlated subqueries when GROUP BY would be awkward.

EXISTS — efficient existence check

Often faster than IN for 'does any row exist'.

sql

-- Users who have at least one post
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

-- Users with NO posts
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

-- EXISTS stops at first match, efficient for big inner tables
-- IN materializes the full inner result, usually slower

CTEs — readable multi-step queries

WITH clauses let you name intermediate results.

sql

WITH user_post_counts AS (
  SELECT user_id, COUNT(*) AS post_count
  FROM posts
  WHERE published = TRUE
  GROUP BY user_id
),
top_posters AS (
  SELECT user_id
  FROM user_post_counts
  WHERE post_count >= 5
)
SELECT u.id, u.name, upc.post_count
FROM users u
JOIN user_post_counts upc ON upc.user_id = u.id
WHERE u.id IN (SELECT user_id FROM top_posters);

Recursive CTE — hierarchies

For trees / graphs / org charts / category hierarchies.

sql

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT,
  parent_id INT REFERENCES categories(id)
);

-- Get the full ancestor chain for category id = 42
WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE id = 42
  UNION ALL
  SELECT c.id, c.name, c.parent_id, a.depth + 1
  FROM categories c
  JOIN ancestors a ON c.id = a.parent_id
)
SELECT * FROM ancestors;

Common mistakes

IN (subquery) on huge tables (slow, use EXISTS or JOIN). Correlated subqueries when GROUP BY would be faster. Treating CTEs as optimization hints, in old Postgres (< 12) CTEs were optimization fences. Modern Postgres inlines them. Forgetting RECURSIVE keyword on recursive CTEs.

←GROUP BY and Aggregations

Back to SQL

Window Functions→