SQL subqueries (IN, EXISTS, correlated)

A subquery is a query written inside another one, between parentheses. It lets you answer two-step questions in one go, such as “which products cost more than the average?”: you first have to compute the average, then compare each product with that value.

Syntax

SELECT columns FROM table
WHERE column > (SELECT AVG(column) FROM table);

WHERE column IN (SELECT column FROM other_table)
WHERE EXISTS (SELECT 1 FROM other_table WHERE …)

How it works

The three most common forms

• A single value: the subquery returns one value, compared with =, >, <…
• A list: the subquery returns a column, used with IN or NOT IN.
• An existence test: EXISTS (…) is true if the subquery returns at least one row.

Correlated subqueries

A correlated subquery refers to the current row of the main query. It is therefore recomputed for each row. Example: comparing each employee's salary with the average of their own department.

In the FROM clause

A subquery can also act as a temporary table in the FROM (you then give it an alias). For complex queries, a CTE (WITH) is often easier to read.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT nom, prix
FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits)
ORDER BY prix DESC;

SELECT prenom, nom, ville
FROM clients
WHERE id IN (SELECT client_id FROM commandes WHERE statut = 'annulée')
LIMIT 8;

SELECT e.prenom, e.nom, e.departement, e.salaire
FROM employes e
WHERE e.salaire > (SELECT AVG(x.salaire) FROM employes x WHERE x.departement = e.departement);

SELECT c.prenom, c.nom
FROM clients c
WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id);

Common mistakes

SELECT * FROM clients WHERE id = (SELECT client_id FROM commandes);

SELECT * FROM clients WHERE id IN (SELECT client_id FROM commandes);

SELECT * FROM employes WHERE id NOT IN (SELECT manager_id FROM employes);

SELECT * FROM employes e WHERE NOT EXISTS (SELECT 1 FROM employes x WHERE x.manager_id = e.id);

SELECT AVG(nb) FROM (SELECT COUNT(*) AS nb FROM commandes GROUP BY client_id);

SELECT AVG(nb) FROM (SELECT COUNT(*) AS nb FROM commandes GROUP BY client_id) AS t;

Exercises

Try writing the query yourself before opening the solution.

SELECT categorie, nom, prix FROM produits p WHERE prix = (SELECT MAX(prix) FROM produits WHERE categorie = p.categorie);

SELECT prenom, nom FROM employes e WHERE NOT EXISTS (SELECT 1 FROM employes x WHERE x.manager_id = e.id);

Frequently asked questions