SQL and MySQL course Β· Module 3: Filtering (WHERE)

Lists of values: IN

2 min read Β·
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. 1. What does the condition mean: WHERE kategoria_id IN (1, 2, 3) ?

  2. 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:

  1. QuestionThe IN operator
  2. QuestionIN as a shortcut for OR
  3. Coding taskAuthors from Poland or Greece
  4. Coding taskSelected categories
  5. Click in orderAuthors from a list of countries

Useful articles