Subqueries, CTEs and UNION

Subqueries

A subquery is a query inside another query, in parentheses.

In WHERE — compare with a value computed from the data. Products priced above the average list price:

SELECT product_name, list_price
FROM products
WHERE list_price > (SELECT AVG(list_price) FROM products)
ORDER BY list_price
LIMIT 6;

With IN — customers who bought during Black Friday 2025:

SELECT COUNT(*) AS black_friday_2025_customers
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
  WHERE campaign_id = 11 AND status = 'completed'
);

In FROM (a derived table) — aggregate first, then query the summary:

SELECT ROUND(AVG(orders_per_customer), 2) AS avg_orders_per_buying_customer
FROM (
  SELECT customer_id, COUNT(*) AS orders_per_customer
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
) AS per_customer;

Correlated subqueries

A correlated subquery refers to the outer query, so it is (logically) evaluated once per outer row. Products whose price is above their own category's average:

SELECT p.product_name, p.category, p.list_price
FROM products AS p
WHERE p.list_price > (
  SELECT AVG(p2.list_price) FROM products AS p2
  WHERE p2.category = p.category
)
ORDER BY p.category, p.list_price
LIMIT 6;

p2.category = p.category links the inner query to the current outer row. Correlated subqueries are expressive but can be slow on big tables; window functions (later) usually do the same job more efficiently.

EXISTS is a correlated pattern that is often fast, because it stops at the first match:

SELECT COUNT(*) AS customers_with_a_return
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'returned');

CTEs: WITH … AS

A common table expression names a subquery and puts it at the top, so the query reads top to bottom like a recipe:

WITH order_revenue AS (           -- step 1: revenue per order (aggregate lines first: no fan-out)
  SELECT order_id,
         SUM(quantity * unit_price * (1 - discount_pct)) AS revenue
  FROM order_items
  GROUP BY order_id
),
completed_2025 AS (               -- step 2: completed 2025 orders with their revenue
  SELECT o.order_id, o.channel, r.revenue
  FROM orders o JOIN order_revenue r ON r.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
)
SELECT channel,                    -- step 3: summarise
       COUNT(*)              AS orders,
       ROUND(SUM(revenue), 0) AS revenue,
       ROUND(AVG(revenue), 2) AS avg_order_value
FROM completed_2025
GROUP BY channel;

UNION and UNION ALL

UNION stacks the results of queries vertically. The queries must return the same number of columns with compatible types.

• UNION ALL keeps every row (fast).
• UNION removes duplicate rows (slower — it must compare rows).

SELECT 'stores' AS source, COUNT(*) AS rows FROM stores
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'customers', COUNT(*) FROM customers;

Practice

Summary

• Subqueries can sit in WHERE (values, IN, EXISTS), FROM (derived tables) and SELECT.
• Correlated subqueries run per outer row — expressive, sometimes slow.
• CTEs (WITH) make multi-step logic readable and testable.
• UNION ALL stacks all rows; UNION removes duplicates.