SQL subqueries and CTEs

In short: A subquery is a query inside another. A CTE (“common table expression”, WITH) names an intermediate query and makes long queries readable.

In more detail: Both solve tasks that cannot be done with a single simple query, such as “customers with above-average revenue”.

In Depth

• Scalar: Returns exactly one value: WHERE amount > (SELECT AVG(amount) FROM orders).
• List: WHERE customer_id IN (SELECT id FROM customer WHERE country = 'DE').
• EXISTS: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) checks whether at least one row exists and stops at the first hit. Often faster and safer than IN, especially against NULL traps (see NULL in SQL).
• Correlated: The subquery refers to the outer row and runs logically per row.
• In FROM: A subquery as a “derived table” that you give an alias.
• CTE: WITH revenue AS (SELECT customer_id, SUM(amount) s FROM orders GROUP BY customer_id) SELECT ... FROM revenue .... Several CTEs in a row structure complex analyses. Recursive: recursive CTEs.
• Choice: Readability counts. Modern optimisers often treat subqueries like joins.

See also: SQL joins, window functions