Skip to main content

​
WITH (CTE)

Common Table Expressions give a name to a subquery that can be referenced multiple times in the main query. CTE using SELECT: 
WITH top_customers AS (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM myschema.orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, tc.lifetime_value
FROM myschema.customers AS c
INNER JOIN top_customers AS tc ON c.id = tc.customer_id
ORDER BY tc.lifetime_value
CTE using VALUES: 
WITH status_labels AS (
  SELECT * FROM (VALUES
    ('pending', 'Awaiting payment'),
    ('shipped', 'On the way'),
    ('delivered', 'Complete')
  ) AS t(code, label)
)
SELECT o.id, sl.label
FROM myschema.orders AS o
INNER JOIN status_labels AS sl ON o.status = sl.code

​
Multiple CTEs

Define multiple CTEs in a single WITH block. Later CTEs may reference earlier ones: 
WITH
  active_orders AS (
    SELECT * FROM myschema.orders WHERE status != 'cancelled'
  ),
  order_totals AS (
    SELECT customer_id, COUNT(*) AS cnt, SUM(total) AS total
    FROM active_orders
    GROUP BY customer_id
  )
SELECT c.name, ot.cnt, ot.total
FROM myschema.customers AS c
INNER JOIN order_totals AS ot ON c.id = ot.customer_id
WHERE ot.cnt >= 3

​
Scalar Subquery in WHERE

A subquery that returns a single value can be used in a comparison: 
SELECT * FROM myschema.orders
WHERE total > (SELECT AVG(total) FROM myschema.orders)

​
Correlated Subquery in WHERE

The subquery references a column from the outer query: 
-- Orders where the total exceeds the customer's average order total
SELECT o.id, o.customer_id, o.total
FROM myschema.orders AS o
WHERE o.total > (
  SELECT AVG(o2.total)
  FROM myschema.orders AS o2
  WHERE o2.customer_id = o.customer_id
)

​
Subquery in FROM (Derived Table)

SELECT sub.customer_id, sub.order_count
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM myschema.orders
  GROUP BY customer_id
) AS sub
WHERE sub.order_count > 10

​
Not Supported

  • WITH RECURSIVE (recursive CTEs)
  • Scalar subquery in the SELECT list (e.g., SELECT (SELECT MAX(x) FROM t), id FROM ...)
  • Subquery in HAVING or ORDER BY