SQL and MySQL course Β· Module 3: Filtering (WHERE)
Combining conditions: AND, OR, NOT
In this lesson4
Often one condition is not enough. The scribe wants scrolls that are at the same time new and available, or authors from Poland or from Greece. To combine conditions we use three words: AND, OR and NOT.
AND - both conditions at once
The AND operator requires that all the joined conditions be satisfied:
SELECT * FROM ksiazki
WHERE rok_wydania > 1900 AND dostepna = 1;This means: "Books published after 1900 AND available at the same time". A row enters the result only when both conditions are true.
OR - one condition is enough
The OR operator requires that at least one of the conditions be satisfied:
SELECT * FROM autorzy
WHERE kraj = 'Polska' OR kraj = 'Grecja';This means: "Authors from Poland OR from Greece". It is enough that at least one condition is true.
NOT - negation
The NOT operator reverses a condition - it returns what does NOT satisfy it:
SELECT * FROM ksiazki
WHERE NOT dostepna = 1;This means: "Books that are NOT available".
Parentheses - order matters
When we mix AND and OR, use parentheses ( ) to state the order clearly. AND has higher priority than OR, so parentheses protect you from a mistake:
SELECT * FROM ksiazki
WHERE (kategoria_id = 1 OR kategoria_id = 2) AND cena > 30;Here we first check the category (one of two), and only then the price. Parentheses work as in mathematics - what is inside is calculated first. Remember them!
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. Which rows will the condition return: WHERE rok_wydania > 1900 AND dostepna = 1 ?
2. Which rows will the condition return: WHERE kraj = 'Polska' OR kraj = 'Grecja' ?
These are 2 of 4 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the ksiazki table for books published after 1900 (rok_wydania greater than 1900) AND at the same time available (dostepna equal to 1).
- Code editor
Write a query that retrieves all columns from the ksiazki table for books from the category with kategoria_id equal to 1 AND whose cena is less than 50.
- Vertical ordering
Arrange the lines of a query filtering with two conditions (from the first line to the last):
- Horizontal ordering
Arrange in one line the condition combining two requirements with the AND operator: