SQL and MySQL course Β· Module 3: Filtering (WHERE)
Lists of values: IN
In this lesson3
When there are many OR conditions, the query becomes long and hard to read. Luckily the Library gives us an elegant shortcut: the IN operator for a list of allowed values.
IN instead of many OR
Instead of writing many OR conditions, we give a list of values in parentheses:
SELECT * FROM ksiazki
WHERE kategoria_id IN (1, 2, 3);This means: "Books whose kategoria_id is 1 OR 2 OR 3". It is the exact equivalent of:
SELECT * FROM ksiazki
WHERE kategoria_id = 1 OR kategoria_id = 2 OR kategoria_id = 3;but much shorter and more readable!
IN with text
The IN operator also works with text - then remember the single quotes around each value:
SELECT * FROM autorzy
WHERE kraj IN ('Polska', 'Grecja', 'Egipt');This means: "Authors from Poland, Greece or Egypt". We separate the values inside the parentheses with commas.
Numbers without quotes
When the list contains numbers, we do not use quotes:
SELECT * FROM ksiazki
WHERE kategoria_id IN (1, 3, 5);Remember: IN is your ally when you want to pick several specific values from one column. A shorter notation, fewer mistakes - the true art of the scribe!
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 does the condition mean: WHERE kategoria_id IN (1, 2, 3) ?
2. Which notation is equivalent to the condition: WHERE kategoria_id = 1 OR kategoria_id = 2 OR kategoria_id = 3 ?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the autorzy table for authors whose kraj is 'Polska' or 'Grecja'. Use the IN operator.
- Code editor
Write a query that retrieves all columns from the ksiazki table for books whose kategoria_id belongs to the list 1, 3, 5. Use the IN operator.
- Click in order
Click the elements in the order in which we build a query with a list of countries: