SQL and MySQL course Β· Module 3: Filtering (WHERE)
Combining many conditions in practice
In this lesson4
You already know all the scribe's tools: comparisons, AND/OR/NOT, IN, BETWEEN, LIKE and IS NULL. Time to combine them in one powerful query. This is where the true power of the Scriptorium shows itself.
Two AND conditions
The simplest combination is two conditions joined with the AND operator. We can combine different tools - for example a BETWEEN range with a comparison:
SELECT * FROM ksiazki
WHERE rok_wydania BETWEEN 1990 AND 2020
AND dostepna = 1;This means: "Books published in the years 1990-2020 AND available at the same time".
Combining different operators
Operators can be freely mixed. Here a price range meets a list of categories:
SELECT * FROM ksiazki
WHERE cena BETWEEN 20 AND 80
AND kategoria_id IN (1, 2);This means: "Books priced from 20 to 80 coins, belonging to category 1 or 2".
Three conditions at once
We can join even three conditions - a range, a list and a pattern:
SELECT * FROM ksiazki
WHERE cena BETWEEN 20 AND 80
AND kategoria_id IN (1, 2)
AND tytul LIKE 'O%';This query will return books priced from 20 to 80 coins, from category 1 or 2, whose title starts with O. Three conditions in one!
Readability matters
When there are many conditions, it is worth writing each one on a separate line and indenting them under WHERE. This way the query becomes as readable as a well-written scroll. Remember the parentheses too when you mix AND with OR. Practice combining - this is the key to mastery!
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 will the query return: SELECT * FROM ksiazki WHERE cena BETWEEN 20 AND 80 AND kategoria_id IN (1, 2) ?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the ksiazki table for books published in the years from 1990 to 2020 (BETWEEN) AND at the same time available (dostepna equal to 1).
- Vertical ordering
Arrange the lines of a complex filtering query (from the first line to the last):