Master SQL subqueries and Common Table Expressions (CTEs) to break down complex data transformations into readable, modular steps.
What it is
A subquery is a query nested inside another query. It allows you to filter or calculate values based on intermediate results without creating temporary tables. A Common Table Expression (CTE), defined using the WITH clause, is a named temporary result set that exists only for the duration of the subsequent query. CTEs improve readability by allowing you to reference the same intermediate logic multiple times within a single statement.
Why it matters
- Readability: CTEs decompose complex logic into smaller, named chunks, making code easier to debug and maintain.
- Reusability: Unlike inline subqueries, a CTE can be referenced multiple times in the main query without rewriting the logic.
- Modularity: They allow you to build step-by-step data pipelines directly in SQL, mimicking functional programming patterns.
- Performance: In many modern databases, CTEs are optimized similarly to subqueries, but they prevent redundant calculations if referenced correctly.
Syntax or steps
The basic structure of a CTE involves defining one or more expressions before the main SELECT statement. The syntax follows this pattern:
WITH cte_name AS (
SELECT ...
FROM ...
)
SELECT *
FROM cte_name;You can chain multiple CTEs by separating them with commas. Subqueries, conversely, are placed directly within WHERE, HAVING, or FROM clauses.
Example
This example calculates the average salary per department and then identifies employees earning above their department's average. We use a CTE for clarity.
-- Step 1: Define a CTE to calculate average salary per department
WITH dept_avg_salaries AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
-- Step 2: Join the original table with the CTE to filter high earners
SELECT
e.employee_name,
e.department_id,
e.salary,
d.avg_salary
FROM employees e
JOIN dept_avg_salaries d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary
ORDER BY e.department_id, e.salary DESC;Explanation: The dept_avg_salaries CTE computes the mean salary for each group. The main query joins the raw employees table with this calculated summary. The WHERE clause filters rows where an individual's salary exceeds the computed average. This approach is cleaner than nesting a subquery inside the WHERE clause repeatedly.
Common mistakes
- Forgetting the comma: When chaining multiple CTEs, ensure each definition is separated by a comma, except the last one.
- Scope errors: A CTE is only visible to the immediate next statement. You cannot reference a CTE defined in one query from a separate query execution.
- Overusing subqueries: Deeply nested subqueries become unreadable. If you find yourself nesting more than two levels deep, refactor using CTEs.
- Column ambiguity: Always alias your tables and columns in CTEs to prevent conflicts when joining back to the source tables.
When to use it
| Feature | Subquery | CTE |
|---|---|---|
| Complexity | Best for simple, one-off filters. | Best for multi-step logic or reusable blocks. |
| Readability | Can become cluttered if nested. | High; separates logic from presentation. |
| Recursion | Not supported in standard SQL. | Supported via RECURSIVE keyword. |
| Performance | Optimizer may flatten it. | Optimizer may materialize or inline it. |
Use subqueries for quick existence checks (e.g., IN clauses). Use CTEs when you need to perform aggregations, window functions, or self-joins that require clear separation of concerns.
Practice
Guided Exercise: Write a CTE that counts the number of orders per customer. Then, select customers who have placed more than 5 orders.
Challenge: Refactor a query that uses three nested subqueries to find the top 3 products by revenue into a single query using two chained CTEs.
Hint: Start by calculating total revenue per product in the first CTE, then rank them in the second.
Quick check
Q: Can a CTE reference itself?
A: Yes, but only if declared as WITH RECURSIVE. Standard CTEs cannot reference themselves.
Summary
CTEs provide a structured way to write complex SQL queries by breaking them into logical, named components. While subqueries are useful for simple filtering, CTEs offer superior readability and reusability for multi-step data analysis tasks.