Chapter 9: Filtering and Operators
Now we’re going deep into the WHERE clause — the part that lets you filter exactly which rows you want to see. Everything we do here is about writing conditions that the database checks for every single row.
We’re going to cover all the important operators and keywords you’ll use 99% of the time:
- Comparison operators (=, >, <, >=, <=, != / <>)
- Logical operators (AND, OR, NOT)
- IN (match any value in a list)
- BETWEEN (match a range of values)
- LIKE (pattern matching with wildcards % and _)
- IS NULL / IS NOT NULL (finding missing values)
We’ll use our trusty bookshop database with the books and authors tables. (If you want to follow along, make sure you have the sample data from previous chapters.)
Sample Data Reminder (books table)
| book_id | title | isbn | author_id | price | stock | created_at |
|---|---|---|---|---|---|---|
| 1 | The Room on the Roof | 9780143333401 | 1 | 250.00 | 45 | 2026-01-01 10:00:00 |
| 2 | 2 States | 9788129115300 | 3 | 299.00 | 120 | 2026-01-02 11:00:00 |
| 3 | The White Tiger | 9781416562603 | 2 | 399.00 | 35 | 2026-01-03 12:00:00 |
| 4 | Wise and Otherwise | 9780143062226 | 5 | 199.00 | 80 | 2026-01-04 13:00:00 |
| 5 | The Immortals of Meluha | 9789380658742 | 4 | 349.00 | 65 | 2026-01-05 14:00:00 |
| 6 | The Blue Umbrella | 9780143333784 | 1 | 180.00 | 50 | 2026-01-06 15:00:00 |
| 7 | Malgudi Days | 9780140183917 | 6 | 220.00 | 90 | 2026-01-07 16:00:00 |
| 8 | Train to Pakistan | 9780140116144 | 7 | 280.00 | 60 | 2026-01-08 17:00:00 |
Let’s start asking smart questions!
1. Comparison Operators – Basic Comparisons
These are the building blocks of every WHERE clause.
| Operator | Meaning | Example Query |
|---|---|---|
| = | Equals | WHERE price = 299.00 |
| > | Greater than | WHERE price > 300.00 |
| < | Less than | WHERE stock < 50 |
| >= | Greater than or equal | WHERE publication_year >= 2000 |
| <= | Less than or equal | WHERE price <= 250.00 |
| != or <> | Not equal | WHERE author_id != 1 (or author_id <> 1) |
Real examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Books costing exactly ₹299 SELECT title, price FROM books WHERE price = 299.00; -- Books more expensive than ₹300 SELECT title, price FROM books WHERE price > 300.00; -- Books with stock less than 50 SELECT title, stock FROM books WHERE stock < 50; |
Pro Tip: For strings, always use single quotes: ‘Mumbai’, not “Mumbai”. For numbers and dates, no quotes needed.
2. Logical Operators – Combining Conditions (AND, OR, NOT)
These let you combine multiple conditions.
| Operator | Meaning | Example |
|---|---|---|
| AND | Both conditions must be true | price > 200 AND stock > 50 |
| OR | At least one condition true | price < 200 OR stock > 100 |
| NOT | Reverses the condition | NOT (price > 300) = price ≤ 300 |
Real examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Books that are affordable AND in good stock SELECT title, price, stock FROM books WHERE price <= 300.00 AND stock >= 50; -- Books that are either very cheap OR very popular (high stock) SELECT title, price, stock FROM books WHERE price < 200.00 OR stock > 80; -- Books NOT written by Ruskin Bond (author_id = 1) SELECT title, author_id FROM books WHERE NOT author_id = 1; |
Important: Use parentheses when mixing AND and OR — it changes the order!
|
0 1 2 3 4 5 6 7 |
-- Wrong: price > 300 OR stock > 50 AND author_id = 1 -- Right: (price > 300 OR stock > 50) AND author_id = 1 |
3. IN – Match Any Value in a List
Instead of writing many OR conditions, use IN.
Syntax:
|
0 1 2 3 4 5 6 |
WHERE column IN (value1, value2, value3, ...) |
Example – Books by specific authors:
|
0 1 2 3 4 5 6 7 8 |
SELECT title, author_id FROM books WHERE author_id IN (1, 3, 5); -- Ruskin Bond, Chetan Bhagat, Sudha Murty |
Negate with NOT IN:
|
0 1 2 3 4 5 6 |
WHERE author_id NOT IN (1, 2); |
4. BETWEEN – Match a Range of Values
Perfect for dates, numbers, etc.
Syntax:
|
0 1 2 3 4 5 6 |
WHERE column BETWEEN value1 AND value2; |
Inclusive — includes both ends.
Examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Books priced between ₹200 and ₹350 SELECT title, price FROM books WHERE price BETWEEN 200.00 AND 350.00; -- Books added in the first week of January 2026 SELECT title, created_at FROM books WHERE created_at BETWEEN '2026-01-01' AND '2026-01-07'; |
Negate:
|
0 1 2 3 4 5 6 |
WHERE price NOT BETWEEN 200 AND 300; |
5. LIKE – Pattern Matching with Wildcards
Used for partial string matches.
Wildcards:
- % → zero or more characters (any number)
- _ → exactly one character
Examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- Books with "The" anywhere in the title SELECT title FROM books WHERE title LIKE '%The%'; -- Books starting with "The" SELECT title FROM books WHERE title LIKE 'The%'; -- Books ending with "Days" SELECT title FROM books WHERE title LIKE '%Days'; -- Books with exactly 5 letters before "States" SELECT title FROM books WHERE title LIKE '_____States'; -- 5 underscores = 5 characters |
Case sensitivity: In MySQL/PostgreSQL, LIKE is usually case-insensitive by default. If you need case-sensitive, use BINARY (MySQL) or ILIKE (PostgreSQL).
6. IS NULL / IS NOT NULL – Finding Missing Values
NULL means “no value” or “unknown” — it’s not the same as 0 or empty string.
Never use = NULL — it won’t work!
Correct way:
|
0 1 2 3 4 5 6 7 |
WHERE column IS NULL -- has no value WHERE column IS NOT NULL -- has some value |
Real example – Suppose we have a column discount that is NULL if no discount:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Books with NO discount SELECT title, discount FROM books WHERE discount IS NULL; -- Books that DO have a discount SELECT title, discount FROM books WHERE discount IS NOT NULL; |
Full Powerful Example – Combining Everything
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- Find top affordable, in-stock books by Indian authors -- with "The" in title or price between 200-300 SELECT b.title, b.price, b.stock, a.name AS author FROM books b INNER JOIN authors a ON b.author_id = a.author_id WHERE a.country = 'India' AND b.stock >= 50 AND (b.price BETWEEN 200.00 AND 300.00 OR b.title LIKE '%The%') AND b.title NOT LIKE '%Train%' ORDER BY b.price ASC LIMIT 5; |
