SQL subquery vs CTE for beginners: what they are and when to use each

"Which employees earn more than the average salary?" Look at what that question needs. First you must know the average. Then you compare each person to it. You cannot write WHERE salary > AVG(salary) directly, because WHERE cannot see an aggregate. You need 2 steps, and SQL has two ways to write them in a single query: a subquery and a CTE.

This guide shows both on one small table. The queries ran in DuckDB, and each output is exactly what it printed. You should already know SELECT, WHERE and GROUP BY from SQL for beginners and the GROUP BY guide.

The table

8 employees in three departments.

CREATE TABLE employees (
  emp_id INTEGER,
  name   VARCHAR,
  dept   VARCHAR,
  salary INTEGER
);

INSERT INTO employees VALUES
  (1, 'Meera',  'Management',  150000),
  (2, 'Arjun',  'Engineering', 120000),
  (3, 'Rohan',  'Sales',        90000),
  (4, 'Kavya',  'Engineering', 125000),
  (5, 'Imran',  'Engineering',  80000),
  (6, 'Nikhil', 'Engineering',  72000),
  (7, 'Sana',   'Sales',        60000),
  (8, 'Dev',    'Sales',        95000);

1The problem: you need a number before you can filter

Start with the number. The average salary is:

SELECT AVG(salary) AS average_salary FROM employees;

average_salary
--------------
99000.0
(1 row)

1. 1Now you want everyone above it. You could copy 99000 into a new query, but then it goes stale the moment someone's salary changes. Better to let the database work it out in the same query.

2A subquery: a query inside a query

Put the first query in brackets inside the second. The database runs the inner one first, gets one number, and uses it in the outer one.

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

name  | salary
------+-------
Meera | 150000
Kavya | 125000
Arjun | 120000
(3 rows)

Three people earn more than 99000. Read it from the inside out: "the average salary" is worked out first, then "everyone whose salary is above that". A subquery that returns one single value, like this one, is called a scalar subquery.

A subquery can also return a list. IN checks whether a value is in that list. Here, the names of people who work in a department that has someone earning over 120000:

SELECT name, dept
FROM employees
WHERE dept IN (SELECT dept FROM employees WHERE salary > 120000)
ORDER BY name;

name   | dept
-------+------------
Arjun  | Engineering
Imran  | Engineering
Kavya  | Engineering
Meera  | Management
Nikhil | Engineering
(5 rows)

Only two people earn more than 120000: Meera in Management and Kavya in Engineering. So the inner query returns two departments, and the outer query keeps everyone who works in either of them. Arjun earns exactly 120000, so he does not qualify himself, but he is listed because his department does.

A subquery can even replace a table in FROM. Here, first work out each department's average, and then look at only the departments above 90000:

SELECT dept, avg_salary
FROM (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
) AS dept_averages
WHERE avg_salary > 90000
ORDER BY dept;

dept        | avg_salary
------------+-----------
Engineering | 99250.0
Management  | 150000.0
(2 rows)

The inner query makes a small temporary table of averages, and the outer query filters it. When you use a subquery in FROM, most databases need you to give it a name, here dept_averages.

3A CTE: the same idea with a name

A CTE (Common Table Expression) starts with WITH. You write the inner query first, give it a name, and then use that name like a table in the main query.

WITH average AS (
  SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM average)
ORDER BY salary DESC;

name  | salary
------+-------
Meera | 150000
Kavya | 125000
Arjun | 120000
(3 rows)

The same three people. The difference is only in how it reads: first "here is the average", then "now use it". The department version reads even better as a CTE:

WITH dept_averages AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
)
SELECT dept, avg_salary
FROM dept_averages
WHERE avg_salary > 90000
ORDER BY dept;

dept        | avg_salary
------------+-----------
Engineering | 99250.0
Management  | 150000.0
(2 rows)

Same result as the subquery in FROM, but the steps are listed top to bottom instead of nested.

4More than one step: where CTEs shine

When a question has 3 steps, nested subqueries turn into brackets inside brackets that are hard to read. A CTE lets you name each step and chain them with commas:

WITH dept_averages AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
),
above_own_dept AS (
  SELECT e.name, e.dept, e.salary, d.avg_salary
  FROM employees e
  JOIN dept_averages d ON d.dept = e.dept
  WHERE e.salary > d.avg_salary
)
SELECT name, dept, salary, ROUND(avg_salary) AS dept_average
FROM above_own_dept
ORDER BY dept, salary DESC;

name  | dept        | salary | dept_average
------+-------------+--------+-------------
Kavya | Engineering | 125000 | 99250.0
Arjun | Engineering | 120000 | 99250.0
Dev   | Sales       | 95000  | 81667.0
Rohan | Sales       | 90000  | 81667.0
(4 rows)

"Who earns more than the average of their own department?" Step one finds each department's average. Step two keeps the people above their own department's number. The last SELECT presents the result. Each step has a name, so you can read the query like a recipe, and you can test each step on its own by running just that part.

5Subquery or CTE?

They give the same answers. The choice is about readability.

A common worry is that a CTE is saved somewhere. It is not. A CTE exists only while its query is running, then it disappears. If you need to keep a result, create a view (a saved query that behaves like a table) or a real table instead. As a habit, start with the shortest version that is easy to read, and switch to a CTE when the nesting makes your head hurt.

What to try next

Rewrite each subquery above as a CTE yourself, then do the reverse, without looking. Then try Top three salaries (free). When you are ready for the next level, the window function guide uses CTEs in almost every example.