Neha Christina

Posted on Apr 2

CTEs vs Subqueries in SQL: Which One Should You Use?

#programming #dataengineering #sql #beginners

If you've written SQL for more than a week, you've hit this question.

Do I use a CTE or a subquery here?

Both can solve the same problem. Both produce the same result. So which one is actually better?

The honest answer: it depends. But there are clear rules for when to use each — and once you know them, you'll never second-guess yourself again.

What is a Subquery?

A subquery is a query nested inside another query. It runs inline, right where it's written.

SELECT name, salary
FROM employees
WHERE salary > (
  -- This is the subquery
  SELECT AVG(salary)
  FROM employees
);

The inner SELECT AVG(salary) runs first, produces a single value, and the outer query uses that value in its WHERE clause.

Subqueries can live in SELECT, FROM, WHERE, and HAVING clauses.

Pros: Quick to write for simple filters. No extra syntax needed.

Cons: Gets hard to read when nested more than one level deep. Can't reuse the result elsewhere in the same query.

What is a CTE?

A CTE (Common Table Expression) is a named temporary result set defined at the top of your query using WITH … AS (). You reference it by name below — just like a real table.

-- Define the CTE first
WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)

-- Now use it like a table
SELECT e.name, e.salary
FROM employees e
JOIN avg_salary a ON e.salary > a.avg_sal;

The CTE is defined once at the top and can be referenced multiple times in the main query.

Pros: Very readable — complex logic is broken into named steps. Can be referenced more than once within the same query.

Cons: Slightly more verbose. Not all databases support recursive CTEs.

Same Query, Two Ways

Here's the same problem — find employees earning above the average salary — written both ways:

Subquery version:

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

CTE version:

WITH avg_sal AS (
  SELECT AVG(salary) AS avg
  FROM employees
)
SELECT e.name, e.salary
FROM employees e
JOIN avg_sal a ON e.salary > a.avg;

Same result. Different readability. The CTE version makes the intent clearer — especially as queries get more complex.

When to Use a Subquery

Use a subquery when the logic is simple and short, you only need it in one place, and you're filtering in WHERE or HAVING. For quick one-liners, subqueries are perfectly fine.

When to Use a CTE

Use a CTE when you need to reference the result more than once, the query is complex and needs to be readable, or you're building step-by-step transformations where each step builds on the last.

CTEs are especially powerful when chained together:

WITH
step_one AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
),
step_two AS (
  SELECT dept, avg_sal
  FROM step_one
  WHERE avg_sal > 70000
)
SELECT * FROM step_two;

Each CTE becomes a building block. Much easier to debug than deeply nested subqueries.

Do CTEs Run Faster?

This is the question most people get wrong.

In most modern databases — including Snowflake and BigQuery — the query optimiser treats CTEs and subqueries almost identically. Neither is consistently faster.

The exception: older versions of Redshift and PostgreSQL sometimes materialise CTEs (compute them separately and store the result), which can make CTEs slower for complex queries. If you're on one of these, test both and measure.

The real rule: optimise for readability first. Only swap to subqueries for performance if you've actually measured a difference.

The Cheat Sheet

	Subquery	CTE
Syntax	Inline	`WITH … AS ()`
Readability	Gets messy	Very readable
Reuse in query?	No	Yes
Performance	Same	Same*
Recursive?	No	Yes
Best for	Simple filters	Complex logic

*Performance varies by database — always test.

Note on reusability: A CTE can be referenced multiple times within the single query it's defined in. It does not persist across queries — for that you'd need a temporary table or a view.

The Simple Decision Rule

If the logic fits in 2–3 lines and you only need it once → subquery.

If the logic is complex, reused, or needs to be understood by someone else → CTE.

When in doubt, use a CTE. Readability is worth more than brevity in team environments.

Which do YOU prefer — CTEs or subqueries? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

DEV Community