Advanced

Subqueries & CTEs

Break complex queries into manageable parts using subqueries, Common Table Expressions (WITH clauses), recursive CTEs, and temporary tables.

Subqueries

A subquery is a query nested inside another query. They can appear in WHERE, FROM, or SELECT clauses.

SQL - Subquery in WHERE
-- Products priced above average
SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

-- Users who have placed orders
SELECT name FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders
);
SQL - Subquery in FROM (Derived Table)
-- Average of category averages
SELECT AVG(avg_price) AS overall_avg
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) category_avgs;

Correlated Subqueries

A correlated subquery references columns from the outer query and executes once per outer row:

SQL
-- Products priced above their category average
SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = p.category
);

-- EXISTS: check if related rows exist
SELECT u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
  AND o.total > 500
);

Common Table Expressions (CTEs)

CTEs use the WITH keyword to define named temporary result sets. They make complex queries readable and maintainable:

SQL
WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(total) 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,
         ROUND(
           (revenue - LAG(revenue) OVER (ORDER BY month))
           / LAG(revenue) OVER (ORDER BY month) * 100, 1
         ) AS growth_pct
  FROM monthly_revenue
)
SELECT * FROM revenue_with_growth
ORDER BY month;
✅
Best practice: Prefer CTEs over nested subqueries. CTEs are easier to read, debug, and maintain. You can also reference a CTE multiple times in the same query.

Recursive CTEs

Recursive CTEs reference themselves, useful for hierarchical data like org charts or category trees:

SQL
WITH RECURSIVE org_chart AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees under each manager
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

Temporary Tables

SQL
-- Create a temp table for intermediate results
CREATE TEMP TABLE high_value_customers AS
SELECT u.id, u.name, SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.total) > 5000;

-- Use it in subsequent queries
SELECT * FROM high_value_customers
ORDER BY lifetime_value DESC;