CTE Fundamentals

Finally, Readable Complex Queries

Nested subqueries are a nightmare to read. You start at the innermost level, work your way out, try to hold the whole thing in your head. There’s a better way.

CTEs (Common Table Expressions) let you name each step and lay them out top-to-bottom. They’re like creating temporary variables that hold query results. Once you start using them, you’ll wonder how you ever lived without them.

Your First CTE

WITH big_orders AS (
  SELECT *
  FROM orders
  WHERE total_order_cost > 200
)
SELECT * FROM big_orders;

You define high_earners first, then use it like a table in the main query. Compare this to the equivalent nested subquery and tell me which you’d rather debug at 11pm.

Basic Syntax

WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT *
FROM cte_name;

The CTE Pattern for Finding Max

Remember the subquery pattern for finding the maximum? Here’s how CTEs make it cleaner:

-- Subquery approach (works, but nested)
SELECT
  company,
  profits
FROM companies
WHERE profits = (
  SELECT MAX(profits)
  FROM companies
);

-- CTE approach (clearer flow)
WITH max_profit AS (
  SELECT MAX(profits) AS highest
  FROM companies
)
SELECT
  c.company,
  c.profits
FROM companies c
JOIN max_profit m
  ON c.profits = m.highest;

The CTE approach separates “find the max” from “find who has it.” Each step is clear and testable.

Most Profitable Financial Company

Use a CTE to find the maximum profit, then join back to find the company.

Why CTEs Beat Nested Subqueries

Here’s a real example. You want each department’s salary as a percentage of company total.

The Nested Way (Hard to Follow)

SELECT
  d.department,
  d.total_salary,
  d.total_salary * 100.0 / NULLIF(c.company_total, 0) AS pct
FROM (
  SELECT
    department,
    SUM(salary) AS total_salary
  FROM employees
  GROUP BY department
) AS d
CROSS JOIN (
  SELECT SUM(salary) AS company_total
  FROM employees
) AS c;

The CTE Way (Clear and Logical)

Same result, but you can read it top-to-bottom: first we calculate department totals, then company total, then combine them.

Average Salaries

Use a CTE to calculate department averages, then JOIN back to show each employee with their department’s average.

CTE Scope

CTEs exist only within their statement. Once the query finishes, they’re gone.

If you need something persistent, you’d create a temporary table or a view. But for most analysis, CTEs are exactly what you need.

Naming Conventions

Name your CTEs well. Good names are documentation.

• customer_orders tells you what’s in it
• temp1 tells you nothing
• dept_avg is clear and concise
• x is lazy and confusing

Pre-Aggregating Before Joining

One of the most common patterns: aggregate data in a CTE, then join it back to add context.

WITH dept_stats AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.first_name,
  e.salary,
  d.avg_salary
FROM employees e
JOIN dept_stats d
  ON e.department = d.department;

Now every employee row shows alongside their department’s average. Want employees above average? Add WHERE e.salary > d.avg_salary.

Highest Salary In Department

Use a CTE to find department-level maximums, then filter or join to find the top earners.

Key Takeaways

• CTEs use WITH to create named temporary result sets
• They make complex queries readable by laying out steps top-to-bottom
• The CTE must come before the main SELECT
• CTEs exist only within their statement
• Name CTEs descriptively; they’re documentation
• The pre-aggregate-and-join pattern is incredibly common

What’s Next

A single CTE is powerful. Multiple CTEs are transformative. Next, you’ll learn to chain CTEs that build on each other, reference the same CTE multiple times, and break complex analysis into logical steps.