Subquery with the IN operator

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

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

First the subquery returns the list of

id

s 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

id

s of the Polish authors and then paste them manually into the query:

1SELECT 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:

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

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

@name