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.

Skip to the 4 exercises ↓

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_salary
WITH dept AS ( … )
Names a query. Until the statement ends, dept can 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.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-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.

Live querySubquery in WHERE: above the company average
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 5 rows
namesalary
Maya72000
Raj95000
Zoe88000
Ivan81000
Kim88000

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.

Live querySubquery in FROM
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
namesalaryavg_salary
Maya7200062666.67
Raj9500088000
Nora4600044500

2. A CTE. The same inner query, lifted out and named. Nothing else changes.

Live queryThe same with WITH
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
namesalaryavg_salary
Maya7200062666.67
Raj9500088000
Nora4600044500

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.

Live queryCorrelated subquery in WHERE
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
namesalary
Maya72000
Raj95000
Nora46000

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.

Live querySubquery in SELECT: the top salary on every row
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namesalarytop_salary
Maya7200095000
Omar5800095000
Lena5800095000
Raj9500095000
Zoe8800095000
Ivan8100095000
Nora4600095000
Theo4300095000
Kim8800095000

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.

Live queryTwo steps: payroll per department, then the big ones
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 4 rows
namedepartment
RajEngineering
ZoeEngineering
IvanEngineering
KimEngineering

When each one reads better

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.

Exercise 1Everyone who earns more than Maya

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE name = 'Maya')
Result · 4 rows
namesalary
Raj95000
Zoe88000
Ivan81000
Kim88000

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.

Exercise 2Who manages someone?

Return the name of every employee who is the manager of at least one other employee, each name once.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name
FROM employees
WHERE id IN (SELECT manager_id FROM employees)
Result · 3 rows
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.

Exercise 3How far below the department's top salary

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
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
Result · 9 rows
namesalaryt.top_salary - e.salary
Maya720000
Omar5800014000
Lena5800014000
Raj950000
Zoe880007000
Ivan8100014000
Nora460000
Theo430003000
Kim880007000

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.

Exercise 4The biggest department, ties included

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
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)
Result · 1 row
departmentpeople
Engineering4

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.

Keep going

Lesson 4 is seven drills on subqueries and CTEs, on a plant shop's customers and orders: a subquery with AVG and with MAX, IN, COUNT(*) versus COUNT(column), the NOT IN trap when the list holds a NULL, and a two-step question solved with WITH. It's free, like lessons 1 to 3 and the level test.

Related

SQL JOIN: INNER and LEFT, with live queriesSQL self join: employees and their managersSQL window functions: OVER and PARTITION BY, live queriesSQL UNION vs UNION ALL: duplicates and totalsAggregate functions are not allowed in WHERESQL interview questions, with answersSQL subquery practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: WITH Queries (Common Table Expressions), PostgreSQL documentation: Value Expressions (scalar subqueries), PostgreSQL documentation: Table Expressions (subqueries in FROM), PostgreSQL documentation: CREATE TABLE (temporary tables), SQLite documentation: The WITH Clause, SQLite documentation: SQL Language Expressions (subquery expressions). Last checked November 2026.