SQL and MySQL course Β· Module 8: Subqueries

Subquery with the IN operator

2 min read Β·
In this lesson3

What do we do when a subquery returns many rows? Then we cannot compare them with a single =. The IN operator comes to the rescue - it checks whether a value is on the list returned by the subquery.

A value on the list

SELECT tytul
FROM ksiazki
WHERE autor_id IN (SELECT id FROM autorzy WHERE kraj = 'Polska');

First the subquery returns the list of ids of all authors from Poland. Then the outer query picks those books whose autor_id is on that list. This way a single query joins two shelves: ksiazki and autorzy.

Cleaner than doing it by hand

Without a subquery we would have to first write down the ids of the Polish authors and then paste them manually into the query:

SELECT tytul FROM ksiazki WHERE autor_id IN (1, 4, 7);

The subquery does that for us - and updates the list itself whenever new authors arrive in the Library.

NOT IN

The NOT IN operator works the other way around - it picks rows whose value is not on the list:

SELECT tytul
FROM ksiazki
WHERE kategoria_id NOT IN (SELECT id FROM kategorie WHERE nazwa = 'Poezja');

This is how we find all books outside the poetry section.

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 is the IN operator combined with a subquery used for?

  2. 2. What will the condition kategoria_id NOT IN (SELECT id FROM kategorie WHERE nazwa = 'Poezja') select?

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves from the ksiazki table the tytul column of those books whose autor_id is on the list of author ids from the autorzy table coming from the country 'Polska'. Use the IN operator and a subquery.

  • Vertical ordering

    Arrange (top to bottom) the lines of the query that retrieves books by authors from Greece using IN and a subquery:

  • Code editor

    Write a query that retrieves from the ksiazki table all columns of books whose autor_id is on the list of author ids from the autorzy table coming from the country 'Grecja'. Use the IN operator and a subquery.

  • Click in order

    Click the elements in the order in which we build a condition with the IN operator and a subquery (one line):

  • Code editor

    Write a query that retrieves from the ksiazki table the tytul column of those books whose kategoria_id is NOT on the list of category ids named 'Poezja' from the kategorie table. Use the NOT IN operator and a subquery.

  1. QuestionThe IN operator with a subquery
  2. QuestionThe NOT IN operator
  3. Coding taskBooks by authors from Poland
  4. Put in orderAn IN subquery for Greek authors
  5. Coding taskBooks by authors from Greece
  6. Click in orderAn IN condition for Polish authors
  7. Coding taskBooks outside poetry

Useful articles