Subqueries and CTEs

What Are Subqueries?

A subquery is a query nested inside another query. It allows you to use the result of one query as an input to another, enabling more complex analytical patterns.

Subqueries can appear in three positions:

• In WHERE: Filter rows based on a calculated result
• In FROM: Use a query result as a virtual table
• In SELECT: Calculate a scalar (single) value alongside each row

Subqueries in WHERE

Use a subquery in WHERE to filter based on a dynamically calculated value:

-- Find orders above the average order value
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount) FROM orders
);

The inner query calculates the average. The outer query uses that average as a filter condition.

IN with Subquery

-- Find customers who placed an order in 2024
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= '2024-01-01'
);

NOT IN with Subquery

-- Find customers who have NOT placed any order in 2024
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= '2024-01-01'
    AND customer_id IS NOT NULL  -- Important: NOT IN with NULLs can cause unexpected results
);

Important: NOT IN with a subquery that returns any NULL values will return no results. Always filter NULLs from the subquery when using NOT IN.

Subqueries in FROM (Derived Tables)

A subquery in FROM creates a temporary virtual table (often called a derived table or inline view):

-- Find the average of each customer's maximum order value
SELECT AVG(max_order) AS avg_of_customer_max
FROM (
  SELECT
    customer_id,
    MAX(total_amount) AS max_order
  FROM orders
  GROUP BY customer_id
) AS customer_max_orders;

You cannot use an aggregate directly on another aggregate (MAX(AVG(...)) is not valid SQL). The subquery in FROM solves this by materialising the intermediate result first.

Common Table Expressions (CTEs)

A CTE (WITH clause) is a named temporary result set defined at the start of a query. CTEs are the preferred modern alternative to subqueries in FROM because they are:

• More readable (named and defined before the main query)
• Reusable within the same query (referenced multiple times)
• Easier to debug (can be tested independently)

Basic CTE Syntax

WITH cte_name AS (
  -- the CTE query
  SELECT customer_id, SUM(total_amount) AS total_spend
  FROM orders
  GROUP BY customer_id
)
-- main query using the CTE
SELECT
  c.name,
  cs.total_spend
FROM customers c
JOIN cte_name cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spend DESC;

Multiple CTEs

WITH
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_month_revenue,
  ROUND((revenue - prev_month_revenue) / prev_month_revenue * 100, 1) AS growth_pct
FROM revenue_with_growth
ORDER BY month;

The second CTE references the first. This chaining makes complex multi-step analysis readable.

EXISTS and NOT EXISTS

EXISTS is often more efficient than IN for checking whether a related record exists:

-- Find customers who have at least one order over $500
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.total_amount > 500
);

-- Find customers with NO orders over $500
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.total_amount > 500
);

EXISTS stops checking as soon as it finds the first matching row (semi-join). It is generally more efficient than IN for large datasets.

Recursive CTEs

A recursive CTE references itself to handle hierarchical or iterative data:

-- Traverse an organisation hierarchy
WITH RECURSIVE org_tree AS (
  -- Base case: top-level employees (no manager)
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees who report to someone in org_tree
  SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY level, name;

Recursive CTEs are used for organisational hierarchies, bill-of-materials, network graphs, and date series generation.

When to Use Each Pattern

Key Takeaways

• Subqueries nest a query inside another query; they can appear in WHERE (filtering), FROM (virtual tables), or SELECT (scalar values).
• CTEs (WITH clause) are the preferred alternative to subqueries in FROM: they are named, readable, reusable within the same query, and easier to debug.
• Multiple CTEs can be chained: the second CTE can reference the first, enabling complex multi-step analysis with clean, readable structure.
• EXISTS/NOT EXISTS is often more efficient than IN/NOT IN for checking related record existence, and avoids the NULL issue that affects NOT IN.
• Recursive CTEs enable hierarchical data traversal -- essential for org charts, bill-of-materials, and other parent-child relationships.

Practice Exercise

Rewrite a complex query using CTEs to make it readable:

1. Write a query (using CTEs) that: calculates monthly revenue, adds a running total, adds month-over-month growth percentage, and flags any month where growth exceeded 20% as "High Growth"
2. Use a subquery in WHERE to find products with a price above the average price in their category
3. Use EXISTS to find customers who have placed an order in every quarter of 2024
4. Write a CTE that segments customers into quartiles by total spend, then counts how many customers are in each quartile