SQL and MySQL course Β· Module 8: Subqueries
Subquery with the IN operator
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. What is the IN operator combined with a subquery used for?
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.