EXISTS and NOT EXISTS

The deepest alley of the Labyrinth holds the EXISTS and NOT EXISTS operators. They do not care how many rows the subquery returns, nor what values they hold - they only ask: does anything exist at all?

EXISTS - is there something?

EXISTS

returns true when the subquery returns at least one row:

1SELECT imie, nazwisko
2FROM autorzy
3WHERE EXISTS (
4  SELECT 1 FROM ksiazki WHERE ksiazki.autor_id = autorzy.id
5);

This query returns authors who have at least one book. Notice

SELECT 1

- with EXISTS it does not matter what we select, because the operator only checks whether any row appears at all.

NOT EXISTS - what is missing?

NOT EXISTS

is the opposite - true when the subquery returns nothing:

1SELECT imie, nazwisko
2FROM autorzy
3WHERE NOT EXISTS (
4  SELECT 1 FROM ksiazki WHERE ksiazki.autor_id = autorzy.id
5);

This is how we find the "silent" creators - authors who left no scroll in the Library.

EXISTS is a correlated subquery

Notice the condition

ksiazki.autor_id = autorzy.id

. EXISTS almost always works with a correlated subquery - for each author on the outside it checks whether a related row exists inside. It is often faster than

IN

, because the Library can stop searching the moment it finds the first matching scroll.

@name