Ephantus Macharia

Posted on Apr 30

Subqueries vs CTEs in SQL: Master Nested Queries and Write Cleaner, Smarter Code

#beginners #database #sql #productivity

If you've been writing SQL for a while, you've hit this wall your query works, but it's a mess of nested parentheses and you can barely read it yourself. That's the moment subqueries and CTEs become your best friends.

Both tools let you break complex logic into manageable steps.

Subqueries

A subquery is a query inside another query. The inner query runs first, and its result is used by the outer query.

The Classic Use Case

Say you want to find all employees earning above the company average:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average first let's say 58,000 then the outer query filters for everyone above that. Simple and effective.

Subquery in the FROM Clause

You can also use a subquery as a temporary table by placing it in the FROM clause:

SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name,
           ROUND(AVG(salary), 2) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summary
WHERE avg_salary > 60000;

The inner query builds a summary table per department. The outer query then filters it. You can't filter on an aggregate alias directly in WHERE, so this pattern is really handy.

Correlated Subquery

A correlated subquery references the outer query it runs once for every row:

SELECT name, salary, department
FROM employees e
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department = e.department
);

For each employee, the inner query finds the highest salary in their department. This gives you the top earner from every department in one query.

Using IN with a Subquery

-- Employees who have made at least one sale
SELECT name
FROM employees
WHERE id IN (
    SELECT DISTINCT employee_id
    FROM sales
);

And the opposite employees who have never made a sale:

SELECT name
FROM employees
WHERE id NOT IN (
    SELECT employee_id
    FROM sales
    WHERE employee_id IS NOT NULL
);

Always filter out NULLs when using NOT IN. If the subquery returns even one NULL, you'll get zero results a silent bug that's easy to miss.

CTEs (Common Table Expressions)

A CTE lets you name a subquery and place it at the top of your statement using WITH. Same result, but much easier to read and maintain.

Basic Syntax

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

Rewriting Our First Example as a CTE

WITH company_avg AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT e.name, e.salary
FROM employees e, company_avg
WHERE e.salary > company_avg.avg_salary;

Same logic, but now the average calculation has a name. Anyone reading this query immediately knows what company_avg means.

Chaining Multiple CTEs

This is where CTEs really shine you can stack them, each building on the previous:

WITH 
dept_totals AS (
    SELECT department,
           SUM(salary)  AS total_salary,
           COUNT(*)     AS headcount
    FROM employees
    GROUP BY department
),
dept_averages AS (
    SELECT department,
           ROUND(total_salary / headcount, 2) AS avg_salary
    FROM dept_totals
),
top_departments AS (
    SELECT department, avg_salary
    FROM dept_averages
    WHERE avg_salary > 65000
)
SELECT *
FROM top_departments
ORDER BY avg_salary DESC;

Read it top to bottom calculate totals, derive averages, filter the top ones.

Reusing a CTE

One thing subqueries can't do cleanly a CTE can be referenced multiple times in the same query:

WITH high_earners AS (
    SELECT * FROM employees
    WHERE salary > 70000
)
SELECT 'Count'         AS metric, COUNT(*)        AS value FROM high_earners
UNION ALL
SELECT 'Total Payroll',           SUM(salary)              FROM high_earners
UNION ALL
SELECT 'Average Salary',          ROUND(AVG(salary), 2)    FROM high_earners;

One definition, three uses. With a subquery you'd repeat the same block three times.

Recursive CTE For Hierarchical Data

CTEs have one trick subqueries simply cannot do recursion. Perfect for org charts, category trees, or any parent-child relationship:

WITH RECURSIVE org_chart AS (
    -- Start: the CEO (no manager above them)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recurse: find everyone who reports to someone already in the CTE
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT name, level
FROM org_chart
ORDER BY level, name;

Output:

name	level
Sarah (CEO)	1
Alice	2
Bob	2
Charlie	3
Diana	3

The query keeps joining until no more reports are found. No loops, no procedural code just SQL.

Takeaways

Subqueries are great for quick, inline logic filtering with IN, comparing against an aggregate, or building a derived table
CTEs shine when your logic is multi-step, needs to be reused, or involves recursion
Both are tools for breaking complex problems into steps picking one is about readability and context, not right vs wrong

DEV Community