SQL practice › SQL reference › Subquery vs CTE
SQL subquery vs CTE: the same question answered three ways
A subquery is a query in parentheses inside another query. A CTE (common table expression) is that inner query moved to the top and given a name: WITH dept AS (SELECT …) SELECT … FROM dept. On this page the two always return the same rows. What changes is how the query reads: a subquery sits at the place where its result is used, a CTE is named first and can then be used like a table, more than once.
Below, one question is answered with a subquery in FROM, with a CTE and with a subquery in WHERE, so you can compare them on the same data.
Syntax
WITH dept AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.salary
FROM employees e
JOIN dept d ON d.department = e.department
WHERE e.salary > d.avg_salaryWITH dept AS ( … )- Names a query. Until the statement ends,
deptcan be read like a table. It isn't stored anywhere: the next statement doesn't know it. SELECT department, AVG(salary) AS avg_salary …- The inner query. Name its computed columns with
AS, because the main query refers to them by name. JOIN dept d ON …- The main query uses the CTE by its name, with an alias if you like.
The table
Nine employees in three departments.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
A subquery that returns one value
The simplest subquery returns one row with one column, and can stand wherever a single value can. It is called a scalar subquery. "Who earns more than the average?" needs the average first, and WHERE salary > AVG(salary) isn't allowed, so the average goes in a subquery.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary |
|---|---|
| Maya | 72000 |
| Raj | 95000 |
| Zoe | 88000 |
| Ivan | 81000 |
| Kim | 88000 |
One question, three ways
A harder question: who earns more than the average of their own department? It takes two steps: the average of each department, then each employee against the average of theirs. The next three queries return the same three people.
1. A subquery in FROM. The first step becomes a table made on the spot (a derived table), and the main query joins to it.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | avg_salary |
|---|---|---|
| Maya | 72000 | 62666.67 |
| Raj | 95000 | 88000 |
| Nora | 46000 | 44500 |
2. A CTE. The same inner query, lifted out and named. Nothing else changes.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | avg_salary |
|---|---|---|
| Maya | 72000 | 62666.67 |
| Raj | 95000 | 88000 |
| Nora | 46000 | 44500 |
3. A subquery in WHERE that looks at the outer row. This one has no join. The subquery mentions e.department, a column of the outer query, so it gives a different answer for each employee: the average of that employee's department. A subquery that refers to the outer query is called a correlated subquery.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary |
|---|---|
| Maya | 72000 |
| Raj | 95000 |
| Nora | 46000 |
A subquery in SELECT
A scalar subquery can also be a column. It has to return a single column and should return a single row.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | top_salary |
|---|---|---|
| Maya | 72000 | 95000 |
| Omar | 58000 | 95000 |
| Lena | 58000 | 95000 |
| Raj | 95000 | 95000 |
| Zoe | 88000 | 95000 |
| Ivan | 81000 | 95000 |
| Nora | 46000 | 95000 |
| Theo | 43000 | 95000 |
| Kim | 88000 | 95000 |
FROM employees x WHERE x.department = e.department and the alias e on the outer table, and each row shows the top salary of its own department.Chaining two CTEs
One WITH can hold several CTEs, separated by commas, and a later one can read an earlier one. That's where a CTE does something a subquery can't do without repeating itself: be used twice. Below, dept is read by big two times, once for its rows and once for the average of its payrolls.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | department |
|---|---|
| Raj | Engineering |
| Zoe | Engineering |
| Ivan | Engineering |
| Kim | Engineering |
When each one reads better
- One value, used once (an average, a maximum, one person's salary): a scalar subquery, right where the value is needed.
- A list to filter by:
WHERE x IN (SELECT …). - A value that depends on the row (the average of this employee's department): a correlated subquery, or a join to a query that computes it for every group.
- A short intermediate table used once: a subquery in
FROMis fine. - An intermediate table used twice, or steps that build on each other: a CTE. Each step gets a name and the query reads in order.
Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data, so a query with the values typed in won't pass. A subquery, a CTE or a join: any query that returns the right rows passes.
Return the name and salary of every employee who earns more than Maya. Don't type her salary: have the query look it up.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE name = 'Maya')| name | salary |
|---|---|
| Raj | 95000 |
| Zoe | 88000 |
| Ivan | 81000 |
| Kim | 88000 |
The subquery returns one value, Maya's salary, and the outer query compares every salary with it. Because the value is looked up, the query stays right when her salary changes.
Return the name of every employee who is the manager of at least one other employee, each name once.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name
FROM employees
WHERE id IN (SELECT manager_id FROM employees)| name |
|---|
| Maya |
| Raj |
| Nora |
The subquery lists every manager_id in the table; an employee manages someone if their own id is in that list. IN answers yes or no per row, so nobody is repeated, as a join would do.
For every employee, return their name, their salary and how much less they earn than the best-paid person of their own department (0 for that person).
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
WITH top AS (
SELECT department, MAX(salary) AS top_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.salary, t.top_salary - e.salary
FROM employees e
JOIN top t ON t.department = e.department| name | salary | t.top_salary - e.salary |
|---|---|---|
| Maya | 72000 | 0 |
| Omar | 58000 | 14000 |
| Lena | 58000 | 14000 |
| Raj | 95000 | 0 |
| Zoe | 88000 | 7000 |
| Ivan | 81000 | 14000 |
| Nora | 46000 | 0 |
| Theo | 43000 | 3000 |
| Kim | 88000 | 7000 |
Step one is the top salary of each department; step two puts it next to each employee and subtracts. A CTE, a subquery in FROM or a correlated subquery in SELECT, (SELECT MAX(x.salary) FROM employees x WHERE x.department = e.department) - e.salary, all give these nine rows.
Return the department with the most employees and its number of employees. If several departments tie for the most, return all of them.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
WITH dept AS (
SELECT department, COUNT(*) AS people
FROM employees
GROUP BY department
)
SELECT department, people
FROM dept
WHERE people = (SELECT MAX(people) FROM dept)| department | people |
|---|---|
| Engineering | 4 |
The counts per department are needed twice: once to find the highest count and once to find who has it. A CTE is written once and read twice. ORDER BY … LIMIT 1 returns a single row even when two departments are level.
Questions people ask
Is a CTE faster than a subquery?
It depends on the database, so choose by how the query reads and measure when speed matters. PostgreSQL's manual says a CTE that is a plain, non-recursive SELECT and is referenced once is folded into the main query and optimized together with it; one referenced more than once is computed once and reused; and the keywords MATERIALIZED and NOT MATERIALIZED override that choice. SQLite's manual says that, without such a hint, SQLite chooses whichever way it thinks is best. For another database, read its manual.
What's the difference between a CTE and a temp table?
A CTE exists only inside the statement that defines it: the next query can't see it. A temporary table is a real table that you create and fill, and several statements can use it; in PostgreSQL it is dropped automatically when the session ends (or, if you ask for it, when the transaction ends). Use a CTE to organize one query, and a temp table when several queries need the same intermediate rows.
What is a correlated subquery?
A subquery that mentions a column of the outer query, such as WHERE x.department = e.department. Its result depends on the outer row, so it can't be run on its own. SQLite's manual says a correlated subquery is evaluated again each time its result is needed, and one that isn't correlated only once.
What happens if a scalar subquery returns more than one row?
It depends on the database, which is a reason to make sure it can't. PostgreSQL's manual says it is an error. SQLite's says the value is the first row of the result, so the query runs and quietly uses one row. Both say that a scalar subquery with no rows gives NULL.
Does a subquery in FROM need an alias?
Give it one, like the d in the first of the three queries. PostgreSQL's manual says the SQL standard requires an alias there; PostgreSQL itself lets you leave it out and recommends writing it, so that the query also works elsewhere.