CTEs vs Subqueries: When to Use Each for Better SQL Performance

I’ve been writing SQL for years, and one thing I’ve noticed is how many developers stick with subqueries for everything. But Common Table Expressions (CTEs) can often make your code much cleaner and sometimes faster too.

Let me show you when you might want to reach for a CTE instead.

Understanding the Basics

So what’s the difference? A subquery is just a query nested inside another query (query-ception, if you will), while a CTE is more like a temporary named result that you can reference in your main query. CTEs only stick around for the statement they’re in.

Here’s how they look in practice:

-- Using a subquery
SELECT e.EmployeeName, e.Department,
    (SELECT COUNT(*) FROM Projects p WHERE p.AssignedTo = e.EmployeeID) AS ProjectCount
FROM Employees e
WHERE e.HireDate > '2024-01-01';

-- Using a CTE
WITH EmployeeProjects AS (
    SELECT e.EmployeeID, COUNT(*) AS ProjectCount
    FROM Employees e
    JOIN Projects p ON p.AssignedTo = e.EmployeeID
    GROUP BY e.EmployeeID
)
SELECT e.EmployeeName, e.Department, ep.ProjectCount
FROM Employees e
LEFT JOIN EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
WHERE e.HireDate > '2024-01-01';

Both get similar results, but they feel completely different when you’re working with them. Let me break down when to use each one.

Key Differences

Here’s the real deal with CTEs vs subqueries:

When CTEs Shine

I reach for CTEs whenever I need to use the same calculated data multiple times. Instead of copy-pasting that big subquery over and over (and praying I don’t miss updating one spot later), I can just define it once and reference it throughout.

CTEs are also a lifesaver for those monster queries with tons of moving parts. Here’s a real example from a sales dashboard I built:

WITH MonthlySales AS (
    SELECT
        YEAR(OrderDate) AS Year,
        MONTH(OrderDate) AS Month,
        SUM(Amount) AS TotalSales
    FROM Orders
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
),
ProductCategorySales AS (
    SELECT
        pc.CategoryName,
        SUM(o.Amount) AS CategorySales
    FROM Orders o
    JOIN Products p ON o.ProductID = p.ProductID
    JOIN ProductCategories pc ON p.CategoryID = pc.CategoryID
    GROUP BY pc.CategoryName
)
SELECT
    ms.Year,
    ms.Month,
    ms.TotalSales,
    pcs.CategoryName,
    pcs.CategorySales
FROM MonthlySales ms
CROSS JOIN ProductCategorySales pcs
ORDER BY ms.Year, ms.Month;

Try writing that with nested subqueries and watch your coworkers run away screaming when you ask for a code review. The CTE version actually makes sense when you come back to it six months later.

When Subqueries Work Better

That said, I still use subqueries all the time for simple stuff. If I just need a quick filter or calculation, a subquery is often cleaner and more direct:

-- Finding employees with above-average salary
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Honestly, this is just easier to read than setting up a whole CTE for something so simple. Sometimes the straightforward approach wins.

The Recursive Power of CTEs

The killer feature of CTEs? Recursion. You simply can’t do this with regular subqueries, and it’s a game-changer for hierarchical data:

-- Finding all employees in a management chain
WITH EmployeeHierarchy AS (
    -- Base case: start with CEO
    SELECT EmployeeID, Name, ManagerID, 1 AS Level
    FROM Employees
    WHERE Title = 'CEO'

    UNION ALL

    -- Recursive case: find all direct reports
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, Name;

Making Your Choice

So how do you decide which to use? Here’s my rule of thumb:

Grab a CTE when your query is getting complex and breaking it into logical chunks would make it easier to understand.

CTEs really shine when you need to use the same calculated data multiple times throughout your query, define it once, use it everywhere.

They’re also your only option when dealing with hierarchical data that requires recursion like org charts or category trees.

And let’s be honest, if you want your colleagues to understand your code without cursing your name in the break room, CTEs make complex logic much more readable.

Stick with subqueries when you’re just doing something quick and simple.

If you just need a basic filter or calculation that you’ll use exactly once, a subquery is often more direct.

No need to complicate things with a CTE when your query is straightforward enough that nesting doesn’t turn it into a jumbled mess. Sometimes the simplest approach is still the best.

Conclusion

Both approaches have their place in your SQL toolkit. I’ve found that CTEs are a lifesaver for those complex queries that would otherwise turn into a nested nightmare. But for quick, one-off calculations, a simple subquery often does the job just fine.

The best SQL developers I know aren’t dogmatic about this stuff, they pick the right tool for each situation. Once you get comfortable with both approaches, you’ll start writing cleaner SQL that runs well and makes sense when you revisit it months later. And trust me, your future self will thank you for that.