VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
EXISTS asks a yes or no question: did this subquery find anything. It does
not care what the subquery returned, only whether there was a row, which makes
it the right tool when you want to test for a related row and none of its
columns.
Open the client with mysql -u root -p sakila before you start.
How EXISTS works
COLUMN_LIST with the columns you want, TABLE_NAME and OTHER_TABLE
with the 2 tables, OUTER_ALIAS and INNER_ALIAS with names for them,
COLUMN_NAME with the columns that link them, and the WHERE inside with
whatever makes a row related.
SELECT 1 is the convention. The select list is ignored, so SELECT 1,
SELECT * and SELECT column behave identically, and 1 says plainly that
nothing is being read.
The subquery is correlated: it names the outer row. The server can stop at the
first matching row, because 1 is enough to answer yes.
Examples
1) Rows that have a match
2) Rows that have none
NOT EXISTS is the other half, and it is how you write an anti-join.
WHERE f.film_id IS NULL, and the 2 forms return the same rows.
NOT EXISTS is safe where NOT IN is not
The IN lesson showsNOT IN returning no rows at all when the
list contains a NULL. NOT EXISTS has no such trap, because it never compares
values: it counts rows.
So when the subquery reads a column that can be NULL, prefer NOT EXISTS.
NOT IN is fine over a column declared NOT NULL, and a liability the moment
that stops being true.
EXISTS never multiplies rows
A join to a table with several matching rows repeats the outer row once per match, which the INNER JOIN lesson shows.EXISTS
cannot do that: it answers yes or no, so each outer row appears once however
many matches there are.
EXISTS, IN and a join can all express “has a related row”, and which is
fastest depends on the data.
Subqueries vs JOINs in MySQL compares them with
plans.
Summary
- Use
EXISTSto test whether a subquery finds any row. - Write
SELECT 1inside, because the select list is ignored. - Use
NOT EXISTSfor the rows with no match. - Prefer
NOT EXISTSoverNOT INwhen the subquery column can be NULL. - Expect no row multiplication, which a join would give you.
See also
- Correlated subqueries — the subquery form EXISTS uses
- IN — the operator with the NULL trap
- LEFT JOIN — the same question asked with a join
- Subqueries vs JOINs in MySQL — which shape the optimizer prefers