11. Subqueries and CTEs · Data Engineering

Subqueries and CTEs

Subqueries and CTEs let one query depend on the result of another query. They are useful when a problem is easier to solve in steps.

Scalar subquery

A scalar subquery returns one value.

sql

SELECT
  order_id,
  total_amount
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount)
  FROM orders
);

This returns orders whose value is above the overall average.

IN subquery

Use an IN subquery when the inner query returns a list.

sql

SELECT *
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE status = "paid"
);

This returns customers who have at least one paid order.

NOT IN and NULL caution

NOT IN can behave unexpectedly if the subquery returns NULL. A safer pattern is often NOT EXISTS.

sql

SELECT *
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

This returns customers with no matching orders.

Derived table

A derived table is a subquery in the FROM clause.

sql

SELECT
  customer_id,
  paid_total
FROM (
  SELECT
    customer_id,
    SUM(total_amount) AS paid_total
  FROM orders
  WHERE status = "paid"
  GROUP BY customer_id
) AS customer_totals
WHERE paid_total >= 500;

CTE with WITH

A CTE names a temporary result set at the top of the query.

sql

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(total_amount) AS paid_total
  FROM orders
  WHERE status = "paid"
  GROUP BY customer_id
)
SELECT
  customer_id,
  paid_total
FROM customer_totals
WHERE paid_total >= 500;

CTEs usually read better than deeply nested derived tables.

Multiple CTEs

sql

WITH paid_orders AS (
  SELECT *
  FROM orders
  WHERE status = "paid"
),
customer_totals AS (
  SELECT
    customer_id,
    SUM(total_amount) AS paid_total
  FROM paid_orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
ORDER BY paid_total DESC;

Use CTEs when each step has a clear meaning and you want the final query to tell a story.