Subqueries and Common Table Expressions

A subquery inside WHERE

SELECT name
FROM students
WHERE score > (SELECT AVG(score) FROM students);

The inner query runs first, producing a single value (the class average), which the outer query then compares each row against. A subquery like this can return a single value, a single column of many values (for use with IN), or an entire table (for use in a FROM clause) — the syntax adapts to where you place it.

A subquery with IN

SELECT name
FROM students
WHERE course_id IN (
  SELECT id FROM courses WHERE level = 'Advanced'
);

Common Table Expressions (CTEs)

WITH high_scorers AS (
  SELECT * FROM students WHERE score >= 90
)
SELECT course, COUNT(*)
FROM high_scorers
GROUP BY course;

A CTE, introduced with WITH, names a temporary result set you can then query exactly like a real table for the rest of that single query. It makes multi-step logic far easier to read than nesting several subqueries inside each other, and you can even chain multiple CTEs together, each one building on the last.

CTEs vs subqueries

A CTE and an equivalent subquery are usually processed identically by the database — the difference is almost entirely about readability. Once a query needs more than one level of nested logic, reach for a CTE; your future self (and your teammates) will read it far faster than a deeply nested subquery.