SQL from Zero › Module 4: DQL: asking questions
AND, OR, IN, BETWEEN and LIKE
Ask sharper questions by combining conditions and matching patterns.
Lesson 20 of 33 · about 15 minutes
Video: Sharper questionsThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
One condition is often not enough. "Fiction books under $13" is two conditions at once.
AND keeps a row only when both conditions are true. OR keeps it when at least one is true:
SELECT title, genre, price
FROM books
WHERE genre = 'Fiction' AND price < 13;
NOT flips a condition: WHERE NOT genre = 'Fiction'.
Use brackets when you mix AND and OR. The database does AND before OR, just like multiplication comes before addition in maths. Brackets make your meaning clear:
WHERE (genre = 'Mystery' OR genre = 'Fantasy') AND price < 12
Shortcuts for common conditions:
| Write | Instead of |
|---|---|
genre IN ('Fantasy', 'Mystery') | genre = 'Fantasy' OR genre = 'Mystery' |
price BETWEEN 10 AND 15 | price >= 10 AND price <= 15 (both ends included) |
LIKE matches patterns in text. % stands for "any characters, or none":
title LIKE 'The %'finds titles starting with "The ".title LIKE '%Sun'finds titles ending in "Sun".title LIKE '%Small%'finds titles containing "Small" anywhere.
In this practice database, LIKE ignores the difference between capital and small letters. Some databases don't, so it's worth knowing.
Key ideas
- AND needs both conditions to be true; OR needs at least one.
- AND is done before OR, so use brackets when you mix them.
- IN lists allowed values, BETWEEN covers a range including both ends, and LIKE matches text patterns with %.
Practise
Loading practice…
Quick check
1. Which books does this return?
SELECT title FROM books WHERE price < 10 OR stock = 0;