Subqueries and CTEs

A subquery is a query in brackets inside another one. In WHERE … IN, it supplies the list:

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM order_items WHERE qty >= 2)
ORDER BY name;

A subquery that returns one value (a scalar subquery) can go anywhere a value can. Products dearer than the average:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

A CTE (common table expression) names a step with WITH name AS (…) and lets the main query use it like a table. You can chain several, separated by commas, and each can use the ones before it. The query then reads top to bottom, one idea per step:

WITH order_totals AS (
  SELECT i.order_id, SUM(i.qty * p.price) AS total
  FROM order_items i
  JOIN products p ON p.id = i.product_id
  GROUP BY i.order_id
)
SELECT order_id, total
FROM order_totals
WHERE total > 80
ORDER BY total DESC;

Subquery or CTE? For the database they're usually the same: most modern ones plan them alike. (PostgreSQL before version 12 always ran a CTE on its own, which could be slower; newer versions fold a simple CTE that is used once into the main query, unless you ask for MATERIALIZED.) For people, a CTE is easier to read, test one step at a time, and reuse within the query.

A correlated subquery refers to the outer query's row, so it's worked out once per row. Each customer's latest order:

SELECT c.name,
  (SELECT MAX(o.ordered_on) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c
ORDER BY c.id;

One trap: x NOT IN (subquery) returns no rows at all if the subquery gives back a single NULL, because "x isn't unknown" is itself unknown. NOT EXISTS doesn't have this problem, so it's the safer way to ask "has no matching row".