Premium

EXISTS and NOT EXISTS

Basic SQL
≈ 1 min read

EXISTS checks whether a row exists

EXISTS does not care about a particular value returned by the subquery. Its question is simpler: did the inner query return at least one row? 

SELECT u.id, u.name
FROM users AS u
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
);

For each user, the query checks whether at least one related order exists. If such a row exists, the user remains in the result.

NOT EXISTS checks for the absence of a match

SELECT u.id, u.name
FROM users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
);

Now the result keeps users for whom the inner query found no orders.

With EXISTS, the existence of a row matters, not the expression after SELECT. That is why SELECT 1 is often used: it emphasizes that the selected values themselves are irrelevant.
Back Next