Lesson 26 of 30

Subqueries in WHERE

Today at the studio

"Who earns more than the average?" asks the board. You write the average on a sticky note, then use the note in a second query. Then you realise the database can do that for you in one go.

The idea

A subquery is a SELECT inside parentheses, used where a value or a list would go. If it returns one value, you can compare against it: salary > (SELECT AVG(salary) FROM staff).

If it returns a column of values, use it with IN: department_id IN (SELECT id FROM departments WHERE ...). The subquery runs first and the outer query uses its result.

Example

SELECT name FROM characters
WHERE show_id IN (SELECT id FROM shows WHERE budget_millions > 4)

Your turn

0 of 4 questions

Question 1 of 4

Who earns more than the average salary? Show name and salary.