Level 15: EXISTS and correlated subqueries

Lesson

The level 14 subqueries are independent: they can be run on their own. A correlated subquery, on the other hand, uses a column of the main query. It cannot run on its own: it is recalculated for each row of the main query.

Example: show the employees who earn more than the average of their department. The average to compare with changes from one employee to another. You write FROM staff s WHERE salary > (SELECT AVG(salary) FROM staff WHERE department = s.department). For Bob (IT), the subquery computes the average of the IT department; for Claire, that of Finance. The link is made through s.department, which comes from the main query.

When the same table appears in both queries, the alias is essential: s stands for the current row of the main query. Without it, department = department would compare the column with itself, and the subquery would compute the average of the whole company.

EXISTS (subquery) is true if the subquery returns at least one row, false otherwise. It answers the question “is there at least one…?”. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) keeps the customers who have at least one order. You write SELECT 1, since only the existence of a row matters, not its content.

NOT EXISTS is true when the subquery returns nothing: the customers without an order. It is the safest way to find what has no match, because EXISTS only answers true or false and is not trapped by NULL, unlike NOT IN.

The link between the two queries is made in the WHERE of the subquery. Forgetting it makes EXISTS true for every row as soon as the queried table is not empty.

Syntax

SELECT …
FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);

Worked example

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

For each customer, the subquery looks for at least one order in their name. Fabio, who ordered nothing, is dropped.

Example result

Key points

• Correlated = the subquery depends on the current row.
• EXISTS: at least one row; NOT EXISTS: none.
• SELECT 1 is enough inside an EXISTS.

Common pitfalls

• Forgetting the linking condition makes EXISTS true for every row.
• Give different aliases to the tables of the query and of the subquery when it's the same table.

The level’s 5 exercises

1. Guided · Show the name of the directors who have at least one movie in the movies table. (Tables: directors, movies)
2. Practice · Show the name of the hotel guests who have never made a booking. (Tables: guests, bookings)
3. Practice · Show the name, department and salary of the employees who earn more than the average of their own department. (Table: staff)
4. Practice · Show the name of the customers who have at least one cancelled order (status = 'cancelled'). (Tables: customers, orders)
5. Challenge · For each genre, show the genre, the title and the rating of the best-rated movie. (Table: movies)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic