DataslopeFiltering Rows with WHERE
Keep only the SQLite rows you care about using WHERE, comparisons, AND, OR, NOT, BETWEEN, IN, and LIKE.
SELECT chooses columns. WHERE chooses rows.
That difference matters. A table may hold hundreds or millions of rows, but most questions only need some of them: books under a certain price, employees in one department, or customers in one city.
WHERE keeps matching rows
A WHERE clause is a test that SQLite applies to each row. If the test
is true, the row stays in the result. If the test is false, the row is
left out.
The equals sign, =, means "is equal to." Text values go in single
quotes.
Comparison operators
SQLite supports the comparisons beginners use constantly:
| Operator | Means | Example |
|---|---|---|
= | equal to | genre = 'Fiction' |
<> or != | not equal to | genre <> 'Travel' |
< and > | less than, greater than | price < 20 |
<= and >= | at most, at least | pages >= 300 |
BETWEEN a AND b | from a through b, inclusive | price BETWEEN 10 AND 20 |
IN (...) | matches any value in a list | genre IN ('Fiction', 'Travel') |
LIKE | matches a text pattern | title LIKE 'Data%' |
BETWEEN includes both ends of the range. In the example, 200 and 320
would both count as matches.
Which clause keeps only rows that pass a test?
SELECT
WHERE
ORDER BY
LIMIT
Combine conditions with AND, OR, and NOT
Real questions often have more than one part.
ANDmeans both conditions must be true.ORmeans at least one condition must be true.NOTflips a condition.
When you mix AND and OR, use parentheses so your meaning is clear.
IN is a shorter OR list
IN checks whether a value is one of several choices. It is often much
cleaner than writing many OR conditions.
LIKE finds text patterns
LIKE compares text to a pattern.
%means any number of characters, including none._means exactly one character.
In SQLite, LIKE is case-insensitive for ordinary ASCII letters by
default. That means 'sql%' can match SQL Street.
Check your understanding
What does BETWEEN 10 AND 20 mean for a price?
More than 10 and less than 20, excluding both ends.
From 10 through 20, including 10 and 20.
Exactly 10 or exactly 20 only.
Any price except 10 through 20.
Which condition keeps books that are either Fiction or Travel?
genre = 'Fiction' AND genre = 'Travel'
genre IN ('Fiction', 'Travel')
genre NOT IN ('Fiction', 'Travel')
genre LIKE 'Fiction Travel'
In SQLite, what does the LIKE pattern 'Data%' match?
Only the exact text Data%.
Text that starts with Data, followed by any characters.
Text that ends with Data.
Only text with exactly one character after Data.