SQL: subqueries and common table expressions

Before you start

SQL GROUP BY and JOIN.

Compare each row with a group average, and name an intermediate result with WITH.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

A subquery is a SELECT used inside another statement. A scalar subquery returns one value, such as the highest salary, and can be used wherever a value is allowed. A common table expression (CTE), introduced by WITH, gives an intermediate result a name so that a complex query can be read in steps, from the inside out.

To find employees who earn more than the average of their own department, first compute each department's average in a CTE, then join it back to the employees and compare. The comparison is a strict greater-than, so an employee exactly at the average is not included. The Eng average is 120, which Ada earns exactly, so only Ed qualifies there.

Code example

SQL

CREATE TABLE emp (name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES ('Ada', 'Eng', 120), ('Bo', 'Ops', 90), ('Cy', 'Eng', 100), ('Di', 'Ops', 80), ('Ed', 'Eng', 140);

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

SELECT name FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

Output

Bo|Ops|90
Ed|Eng|140
Ed

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE emp (name TEXT, dept TEXT, salary INTEGER);

INSERT INTO emp VALUES ('Ada', 'Eng', 120), ('Bo', 'Ops', 90), ('Cy', 'Eng', 100), ('Di', 'Ops', 80), ('Ed', 'Eng', 140);

4WITH dept_avg AS (

5 SELECT dept, AVG(salary) AS avg_salary FROM emp GROUP BY dept

6)

7SELECT e.name, e.dept, e.salary

8FROM emp e

9JOIN dept_avg d ON d.dept = e.dept

10WHERE e.salary > d.avg_salary

11ORDER BY e.name;

13SELECT name FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Name an intermediate result: one row per department with its average salary (Eng 120, Ops 85).	`—`	—
Attach each employee's department average to the employee row.	`—`	—
Keep only employees strictly above their own department's average.	`—`	—
Bo earns 90 against an Ops average of 85, and Ed earns 140 against an Eng average of 120.	`—`	Bo\|Ops\|90 Ed\|Eng\|140
The scalar subquery returns 140, and the outer query finds who earns it.	`—`	Bo\|Ops\|90 Ed\|Eng\|140 Ed

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

Cost and common mistakes

One pass to compute the averages (O(n)), a join (O(n)), and a sort for ORDER BY (O(r log r)).

A scalar subquery that returns more than one row is an error in most databases. SQLite silently uses the first row, which hides mistakes.
AVG of integers returns a decimal, so the comparison uses exact averages, not rounded ones.
CTEs improve readability. Whether they run faster depends on the database's optimizer.

Try it yourself

Who is above the average of the whole company?

Reveal answer and reasoning

The company average is (120 + 90 + 100 + 80 + 140) / 5 = 106, so Ada (120) and Ed (140) are above it.