Write Cleaner SQL: Understanding CTEs and Subqueries the Easy Way

SQL gives us multiple ways to structure complex logic, and two of the most common techniques are Subqueries and Common Table Expressions (CTEs). Both help you break down queries, reuse logic, and improve readability, but they work differently and shine in different situations.

This guide explains what each technique does, how they compare, which one performs better, and the limitations you should know before choosing between them.

 What Is a Subquery?

A subquery is simply a query inside another query. Think of it as a small helper query that runs first and passes its result to the main query. You can place a subquery in the SELECT, FROM, or WHERE clause, depending on what you’re trying to calculate or filter.

For example:

SELECT *

FROM Employees

WHERE Salary > (

    SELECT AVG(Salary)

    FROM Employees

);

In this case, the subquery calculates the average salary, and the outer query uses that value to return employees who earn more than the average.

Subqueries are most helpful when you need quick, focused logic inside a larger query. They’re perfect for simple filtering, like checking whether a value is higher, lower, or present in another result set. They also work well for one‑time calculations, especially when you want to compute something on the fly—such as an average, a count, or a maximum - without creating a separate table or CTE. And because subqueries sit neatly inside your main query, they’re great for inline decision‑making, where you want the database to evaluate a small piece of logic right at the moment you need it, without adding extra structure or complexity.

 What Is a CTE (Common Table Expression)?

A CTE, or Common Table Expression, is basically a temporary, named result set that you create using the WITH keyword. Think of it as giving a nickname to a small query so you can use it later in a bigger query. It doesn’t store data anywhere permanently — it just helps you break your logic into clean, readable steps. This makes your SQL much easier to understand, especially when you’re working with complex joins, filters, or calculations.

Here’s a simple example:

WITH AvgSalary AS (

    SELECT AVG(Salary) AS Avgsal

    FROM Employees

)

SELECT *

FROM Employees e

JOIN AvgSalary a ON e.Salary > a.Avgsal;

In this example, the CTE (AvgSalary) calculates the average salary once, gives it a name, and then the main query uses that result to find employees who earn more than the average. It’s cleaner, easier to read, and avoids repeating the same logic.

CTEs are incredibly useful when your query starts getting long or complicated. They help you break things into logical steps so you can understand what’s happening at each stage. They’re great when you need reusable logic, because you can reference the same CTE multiple times without rewriting the query. CTEs also shine in recursive queries, like working with hierarchical data (employees and managers, folder structures, family trees). And overall, they make your SQL feel more organized and readable — almost like writing your query in chapters instead of one giant paragraph.

CTE vs Subquery: 

Feature	Subquery	CTE
Readability	Can get messy when nested	Very readable
Reusability	No	Yes
Recursion	No	Yes
Performance	Similar in many cases	Similar, but clearer for complex logic
Debugging	Harder	Easier
Ideal For	Simple logic	Complex, multi‑step logic

Performance: Which One Is Faster?

Most modern databases are smart enough to optimize CTEs and subqueries in very similar ways, so in everyday queries you usually won’t see a big performance difference. But there are a few situations where one can be slower than the other.

• CTEs may be slower when the database treats them as optimization barriers

In some older SQL Server versions, the optimizer doesn’t look inside a CTE to push filters or improve the plan. It treats the CTE like a fixed result set, which can make the query run less efficiently than expected.

• CTEs may be slower when the same CTE is referenced multiple times

If you use the same CTE more than once, some databases re‑execute it each time instead of caching the result. This can add unnecessary overhead, especially with large datasets.

• Subqueries may be slower when they are deeply nested

When subqueries are stacked inside each other, the optimizer has to work harder to untangle the logic. This can lead to slower execution, especially if the nesting becomes complex.

• Subqueries may be slower when the same logic is repeated multiple times

If you copy‑paste the same subquery in several places, the database may evaluate it repeatedly. This wastes processing time and can slow down the entire query.

Limitations

Subquery Limitations

• Hard to read when nested

• Cannot be reused

• No recursion

• Can confuse the optimizer in complex cases

CTE Limitations

• Some databases re‑execute CTEs multiple times

• Cannot be indexed

• Not ideal for very simple logic

• Older MySQL versions (< 8.0) don’t support CTEs

  
  

Database Support

 CTEs Supported In:

• SQL Server

• PostgreSQL

• Oracle

• MySQL 8.0+

• MariaDB 10.2+

• Snowflake

• BigQuery

• Redshift

 Subqueries Supported In:

• All SQL databases

  
  

Which One Should You Use?

Use CTE when:

• Query is long or complex

• You want clean, readable SQL

• You need recursion

• You want to reuse logic

Use Subquery when:

• Logic is simple

• You don’t need to reuse results

• You want a compact inline expression

  
  

 Conclusion

Both CTEs and subqueries are essential SQL tools. Subqueries work well for quick, straightforward logic, while CTEs make complex queries easier to read and manage. Picking the right one keeps your SQL cleaner, faster, and easier to maintain.