CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 2 of 5 · 1 min read

WITH as a named subquery

Giving a subquery a name, usable once or several times in the main query.

Think of defining a term once, at the top of a document. Every later mention just refers to that term by name, instead of re-explaining it each time.

In plain words: a CTE, written with WITH, names a subquery's result so the main query can refer to it like a table. A CTE is computed once and can be referenced multiple times in the query that follows it, which plain nested subqueries cannot do without repeating the same subquery text.

A worked example. Finding customers with more than the average number of orders, named step by step instead of nested.

The key lines.

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, order_count
FROM order_counts
WHERE order_count > (SELECT AVG(order_count) FROM order_counts);

Common table expression (CTE)

A common table expression names a subquery's result, using WITH name AS (...), so the rest of the query can refer to it like a table, including more than once.

Example: order_counts above is computed once and referenced twice, in the main SELECT and inside the AVG subquery, without repeating its definition.