Free SQL queries lessonsSQL queries · Stage 4 · Subqueries and window functions · Step 1

A query inside a query

Use a subquery in WHERE with a comparison or IN, and beware NOT IN with NULLs. About 9 minutes.

Ask the inner question first

"Who scored above the class average?" has two questions inside it. What is the average? Then who beat it?

A subquery is a SELECT in brackets, used as a value. SQL runs the inner one first, then the outer one uses its answer. A subquery that returns a single number can go on the right of a comparison. One that returns a column of values can go after IN.

Subquery: a SELECT in brackets, run first, used as a value.

In code

SELECT student
FROM marks
WHERE score > (
  SELECT AVG(score) FROM marks
);

SELECT name
FROM students
WHERE city IN (SELECT city FROM hubs);

Scores above the class average

marks

student	score
Asha	80
Ravi	55
Meera	70
Kabir	95

The inner query runs first: the average of 80, 55, 70 and 95 is 75.

Move 1 of 5

NOT IN has a NULL trap

x IN (1, 2) is true when x is 1 or 2. x NOT IN (1, 2) is true for any other x. Now put a NULL in the list: x NOT IN (1, 2, NULL). Comparing x with NULL is unknown, so the test can never be true, for any x. The query returns no rows, with no error.

It happens when the subquery's column can hold NULL. Either add WHERE col IS NOT NULL inside the subquery, or use NOT EXISTS, which you'll meet next.

NOT IN with a NULL in the list: returns nothing.

In code

WHERE id NOT IN (
  SELECT customer_id FROM orders
  WHERE customer_id IS NOT NULL
)

Quick check

The subquery returns the list 1, 2 and NULL. For x = 5, what does x NOT IN (that list) give?

ANot true: the row is not kept
BTrue: 5 is not 1 or 2
CAn error

Show the answer

Not true: the row is not kept. 5 compared with NULL is unknown, so NOT IN can't be true, and WHERE drops the row.

Your problem

Above the class average

The marks table has one row per student, and a score that is NULL until it is marked. Show the student and score of everyone whose score is above the average of all marked scores. Order by score, highest first, then by student.

Tables

marks(id, student, score)

Up to 2,000 students · names are unique

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.