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
Quoted CTE names and quoted CTE column lists are also supported: 
WITH "sales-data" ("order-id", "Safety.Warning") AS VALUES (
  (1, 'green'),
  (2, 'yellow')
)
SELECT "order-id", "Safety.Warning"
FROM "sales-data"
ORDER BY "order-id"

​
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

​
Scalar Subquery in SELECT List

A subquery that returns a single value can appear as a column expression: 
SELECT
  o.id,
  o.total,
  (SELECT AVG(total) FROM myschema.orders) AS avg_total
FROM myschema.orders AS o
Correlated scalar subqueries in the SELECT list are also supported: 
SELECT
  c.name,
  (SELECT COUNT(*) FROM myschema.orders AS o WHERE o.customer_id = c.id) AS order_count
FROM myschema.customers AS c

​
Subquery in HAVING

Scalar subqueries can be used in HAVING predicates: 
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > (SELECT AVG(cnt) FROM (
  SELECT COUNT(*) AS cnt FROM myschema.orders GROUP BY customer_id
) AS t)

​
Subquery in ORDER BY

Scalar subqueries can be used in ORDER BY expressions: 
SELECT name FROM myschema.customers AS c
ORDER BY (SELECT COUNT(*) FROM myschema.orders AS o WHERE o.customer_id = c.id) DESC

​
Not Supported

  • WITH RECURSIVE (recursive CTEs)