SQL and MySQL course Β· Module 8: Subqueries

EXISTS and NOT EXISTS

2 min read Β·
In this lesson3

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:

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

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:

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

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.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What does the EXISTS operator return?

  2. 2. Why do we often write SELECT 1 inside an EXISTS subquery?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves from the autorzy table the imie and nazwisko columns of those authors who have at least one book. Use EXISTS and a subquery referencing autorzy.id (ksiazki.autor_id = autorzy.id).

  • Horizontal ordering

    Arrange in one line (left to right) the start of a condition with the NOT EXISTS operator:

  • Code editor

    Write a query that retrieves from the autorzy table (alias a) all columns of those authors who have no book at all. Use NOT EXISTS and a subquery referencing a.id (k.autor_id = a.id), where ksiazki has the alias k.

  1. QuestionThe EXISTS operator
  2. QuestionSELECT 1 in EXISTS
  3. QuestionAuthors with no books
  4. Coding taskAuthors who have books
  5. Put in orderA NOT EXISTS condition
  6. Coding taskAuthors with no books
  7. QuestionEXISTS vs IN

Useful articles