SQL Subquery vs CTE: Differences and When to Use

A subquery is a query nested inside another query. A CTE (Common Table Expression) is a named temporary result that you define at the top with WITH and then use like a table.

Both let one query build on the result of another. For many problems either works, so the real question is which one to choose. This guide solves the same problems both ways so you can see the difference for yourself.

We will use this employees table. Ravi and Meera earn the same on purpose.

CREATE TABLE employees (name VARCHAR(50), department VARCHAR(30), salary INT);
INSERT INTO employees VALUES
  ('Asha',  'Sales', 55000),
  ('Ravi',  'Sales', 70000),
  ('Meera', 'Sales', 70000),
  ('Kabir', 'IT',    90000),
  ('Neha',  'IT',    80000),
  ('Arjun', 'HR',    45000),
  ('Divya', 'HR',    55000);

CREATE TABLE staff (emp_id INT, name VARCHAR(50), manager_id INT);
INSERT INTO staff VALUES
  (1, 'Asha', NULL), (2, 'Ravi', 1), (3, 'Meera', 1), (4, 'Kabir', 2), (5, 'Neha', 4);

What is a subquery?

A subquery sits inside parentheses within another statement. Suppose you want employees who earn more than the company average. The inner query works out the average first:

SELECT AVG(salary) AS company_avg
FROM employees;

Now nest it inside the outer query and compare each salary with that value:

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

The database runs the inner query first, gets one number, and then uses it in the WHERE clause. For a single value like this, a subquery is short and perfectly readable.

Subqueries come in a few common shapes:

• Scalar subquery – returns one value, as above.
• List subquery – returns a column of values, used with IN or NOT IN.
• Derived table – a subquery in the FROM clause that acts like a temporary table.
• Correlated subquery – refers to the outer query, so it is re-evaluated for each outer row.

What is a CTE?

A CTE gives a query step a name. You define it with WITH name AS (...), then use that name in the main query as if it were a table:

WITH name AS (
  SELECT ...
)
SELECT ...
FROM name;

Here is a harder question: which employees earn more than the average of their own department? First compute each department’s average, then compare:

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_salary;

Read it top to bottom. Step one, dept_avg works out each department’s average. Step two, the main query joins each employee to their department’s average and keeps those above it. Kabir (90000) beats IT’s 85000, but Neha (80000) does not.

The same problem, written both ways

Department average: correlated subquery

The same result without a CTE, using a correlated subquery that looks up the department average for each row:

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

It returns the same four people. It is compact, but you have to read it from the inside out, and the database conceptually re-runs the inner query for each employee.

Departments above the company average

Now a two-step question: which departments have an average salary above the company average? With a CTE:

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT department, avg_salary
FROM dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employees);

And the same thing with a subquery in the FROM clause:

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employees);

Same answer, but notice how the CTE version separates “compute department averages” from “keep the high ones,” while the subquery version packs both into one block. With two steps the difference is small. With five steps, nested subqueries become very hard to follow, while a chain of CTEs still reads like a recipe.

Recursive CTEs: what a subquery cannot do

A CTE can refer to itself, which lets you walk through hierarchies such as an org chart, where each person has a manager and you do not know how many levels there are. A plain subquery cannot do this.

Here is a staff table where manager_id points to another row:

WITH RECURSIVE org AS (
  SELECT emp_id, name, 1 AS level
  FROM staff
  WHERE manager_id IS NULL

  UNION ALL

  SELECT s.emp_id, s.name, o.level + 1
  FROM staff s
  JOIN org o ON s.manager_id = o.emp_id
)
SELECT name, level
FROM org
ORDER BY level, name;

The first part starts from the person with no manager (Asha, level 1). The second part repeatedly finds the people who report to someone already found, adding 1 to the level each time, until nobody new turns up. In SQL Server you write WITH without the word RECURSIVE.

Subquery vs CTE at a glance

Neither is a stored object. Both disappear as soon as the statement finishes.

What about performance?

Do not assume a CTE is faster or slower than an equivalent subquery. It depends on the database and its version. Some databases treat a CTE exactly like a subquery, while others may calculate it once and store the result temporarily. Write the clearest version first. If a query is slow, look at its execution plan with EXPLAIN rather than guessing.

Which one should you use?

Reach for a subquery when:

• You need a single value, like an average, or a simple IN or EXISTS check.
• The query is short and a WITH block would add more ceremony than clarity.

Reach for a CTE when:

• The logic has several steps, and you want each one named.
• You need the same intermediate result more than once.
• You need recursion.
• Someone else will read and maintain the query.

In interviews, being able to solve a problem both ways and explain why you chose one shows you understand the query rather than a memorised pattern. Practise both in our free SQL compiler.

Frequently asked questions

Is a CTE faster than a subquery?

Not necessarily. Performance depends on the database engine and version. In many cases the optimiser treats them the same way. Choose based on readability, and use EXPLAIN to check a slow query.

Can I use a CTE more than once in a query?

Yes. You can reference the same CTE several times in the main query, and you can define several CTEs one after another, separated by commas. With a subquery you would have to copy it each time.

Is a CTE the same as a temporary table?

No. A CTE exists only for the single statement that follows it and is not stored anywhere. A temporary table is created explicitly and lasts for your session.

What is a correlated subquery?

A subquery that refers to a column from the outer query, so it is evaluated once for each outer row. The department-average example above is a correlated subquery.

Can I use a CTE with UPDATE or DELETE?

In many databases, yes. You can put a WITH clause before an UPDATE or DELETE, though support and syntax vary. Check your database’s documentation.

Related reading

• SQL Window Functions Explained – another clean way to compare each row with its group.
• SQL Joins Explained – the CTE examples above join a CTE to a table.
• SQL WHERE vs HAVING – filtering before and after grouping.