You've already used basic WHERE in the Read/SELECT lesson. This goes deeper — combining multiple conditions correctly, understanding operator precedence, and sorting with more control.
We'll use an expanded books table:
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255),
genre VARCHAR(100),
price DECIMAL(6,2),
published_year INT,
in_stock BOOLEAN DEFAULT TRUE
);INSERT INTO books (title, author, genre, price, published_year, in_stock) VALUES
('Dune', 'Frank Herbert', 'Sci-Fi', 15.50, 1965, TRUE),
('1984', 'George Orwell', 'Dystopian', 9.99, 1949, TRUE),
('Animal Farm', 'George Orwell', 'Satire', 7.50, 1945, FALSE),
('Brave New World', 'Aldous Huxley', 'Dystopian', 10.25, 1932, TRUE),
('Foundation', 'Isaac Asimov', 'Sci-Fi', 12.00, 1951, FALSE);mysql> SELECT * FROM books; +----+-----------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-----------------+---------------+-----------+-------+----------------+----------+ | 1 | Dune | Frank Herbert | Sci-Fi | 15.50 | 1965 | 1 | | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | | 4 | Brave New World | Aldous Huxley | Dystopian | 10.25 | 1932 | 1 | | 5 | Foundation | Isaac Asimov | Sci-Fi | 12.00 | 1951 | 0 | +----+-----------------+---------------+-----------+-------+----------------+----------+
1. Combining conditions with AND / OR
AND requires both conditions to be true. OR requires at least one.
-- Both conditions must be true
SELECT * FROM books
WHERE genre = 'Sci-Fi' AND in_stock = TRUE;+----+-------+---------------+--------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-------+---------------+--------+-------+----------------+----------+ | 1 | Dune | Frank Herbert | Sci-Fi | 15.50 | 1965 | 1 | +----+-------+---------------+--------+-------+----------------+----------+
-- Either condition can be true
SELECT * FROM books
WHERE genre = 'Dystopian' OR genre = 'Satire';+----+-----------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-----------------+---------------+-----------+-------+----------------+----------+ | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | | 4 | Brave New World | Aldous Huxley | Dystopian | 10.25 | 1932 | 1 | +----+-----------------+---------------+-----------+-------+----------------+----------+
2. Operator precedence — the mistake almost everyone makes
AND is evaluated before OR, the same way multiplication happens before addition in math. This can produce surprising results if you're not careful.
-- Looks like: "Orwell books, OR anything published after 1950 that's Sci-Fi"
-- But this actually runs as: (author = 'George Orwell') OR (genre = 'Sci-Fi' AND published_year > 1950)
SELECT * FROM books
WHERE author = 'George Orwell' OR genre = 'Sci-Fi' AND published_year > 1950;Fix: use parentheses to make your intent explicit.
-- Now it's unambiguous: Orwell books published after 1950, in Sci-Fi genre
SELECT * FROM books
WHERE (author = 'George Orwell' OR genre = 'Sci-Fi') AND published_year > 1950;Rule of thumb: any time you mix AND and OR in the same WHERE, add parentheses — even if you're confident about precedence. It makes the query readable for anyone else (including future you).
3. NOT
Inverts a condition:
SELECT * FROM books
WHERE NOT genre = 'Sci-Fi';
-- Equivalent, and more common in practice
SELECT * FROM books
WHERE genre != 'Sci-Fi';+----+-----------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-----------------+---------------+-----------+-------+----------------+----------+ | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | | 4 | Brave New World | Aldous Huxley | Dystopian | 10.25 | 1932 | 1 | +----+-----------------+---------------+-----------+-------+----------------+----------+
NOT is more useful combined with other operators:
Books NOT published in the Sci-Fi or Dystopian genres:
SELECT * FROM books
WHERE genre NOT IN ('Sci-Fi', 'Dystopian');+----+-------------+---------------+--------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-------------+---------------+--------+-------+----------------+----------+ | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | +----+-------------+---------------+--------+-------+----------------+----------+
4. Filtering with BETWEEN
Inclusive range check — includes both endpoints:
SELECT * FROM books
WHERE published_year BETWEEN 1940 AND 1960;Equivalent to:
SELECT * FROM books
WHERE published_year >= 1940 AND published_year <= 1960;+----+-------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-------------+---------------+-----------+-------+----------------+----------+ | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | | 5 | Foundation | Isaac Asimov | Sci-Fi | 12.00 | 1951 | 0 | +----+-------------+---------------+-----------+-------+----------------+----------+
5. Pattern matching with LIKE
% matches any number of characters, _ matches exactly one character.
Titles starting with "The"
SELECT * FROM books WHERE title LIKE 'The%';Titles containing "world" anywhere
SELECT * FROM books WHERE title LIKE '%world%';Titles ending in "ing"
SELECT * FROM books WHERE title LIKE '%ing';Exactly one character before "ave" — matches "Cave", "Save", not "Brave"
SELECT * FROM books WHERE title LIKE '_ave';Matching a literal % or _: if the text you're searching for actually contains a percent sign or underscore, escape it with ESCAPE:
-- Find titles literally containing "50%" (not "any characters" + "50" + "any characters")
SELECT * FROM books WHERE title LIKE '%50\%%' ESCAPE '\';Case sensitivity: by default, MySQL's LIKE is usually case-insensitive for typical text columns (depends on the column's collation) — 'dune' matches 'Dune'. For strict case-sensitive matching, use LIKE BINARY:
SELECT * FROM books WHERE title LIKE BINARY 'dune'; -- won't match "Dune"Performance note: LIKE 'text%' (wildcard only at the end) can use an index and stays fast on large tables. LIKE '%text%' (wildcard at the start) cannot use a standard index — MySQL has to scan every row. More on this in Module 04 — Performance.
6. REGEXP — full pattern matching
For patterns more complex than % and _ can express, MySQL supports regular expressions via REGEXP (alias: RLIKE):
-- Titles starting with "D" or "F"
SELECT * FROM books WHERE title REGEXP '^[DF]';
-- Titles containing a digit
SELECT * FROM books WHERE title REGEXP '[0-9]';
-- Titles that are exactly 4 letters, nothing more
SELECT * FROM books WHERE title REGEXP '^[A-Za-z]{4}$';| Tool | Best for |
|---|---|
LIKE | Simple prefix/suffix/contains checks — faster, can use indexes when the wildcard isn't at the start |
REGEXP | Complex patterns (character sets, repetition, anchors) — more powerful, but never uses a standard index |
7. EXISTS — checking for related rows
Grouped here alongside IN and BETWEEN as another commonly confused filtering operator: EXISTS checks whether a subquery returns any rows at all, without caring about the actual values.
Authors who have at least one book
SELECT * FROM authors a
WHERE EXISTS (
SELECT 1 FROM books b WHERE b.author_id = a.id
);EXISTS often performs better than IN with a subquery on large datasets, since it can stop as soon as it finds one match instead of building a full list first.
5. Filtering with IN
Matches against a list — much cleaner than chaining several OR conditions:
Instead of this:
SELECT * FROM books
WHERE author = 'George Orwell' OR author = 'Isaac Asimov';Write this:
SELECT * FROM books
WHERE author IN ('George Orwell', 'Isaac Asimov');+----+-------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-------------+---------------+-----------+-------+----------------+----------+ | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | | 5 | Foundation | Isaac Asimov | Sci-Fi | 12.00 | 1951 | 0 | +----+-------------+---------------+-----------+-------+----------------+----------+
6. Handling NULL correctly
NULL means "unknown" or "missing" — it can't be compared with =. This trips up almost everyone at some point.
WRONG — this never matches, even if genre is actually NULL:
SELECT * FROM books WHERE genre = NULL;CORRECT:
SELECT * FROM books WHERE genre IS NULL;
SELECT * FROM books WHERE genre IS NOT NULL;7. Sorting with multiple columns
The second column only matters when the first has ties:
Sort by genre alphabetically, then cheapest first within each genre:
SELECT * FROM books
ORDER BY genre ASC, price ASC;+----+-----------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-----------------+---------------+-----------+-------+----------------+----------+ | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 4 | Brave New World | Aldous Huxley | Dystopian | 10.25 | 1932 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | | 5 | Foundation | Isaac Asimov | Sci-Fi | 12.00 | 1951 | 0 | | 1 | Dune | Frank Herbert | Sci-Fi | 15.50 | 1965 | 1 | +----+-----------------+---------------+-----------+-------+----------------+----------+
8. Sorting by a calculated expression
You can sort by something that isn't even a plain column:
Sort by how many years since publication (oldest first):
SELECT title, published_year
FROM books
ORDER BY (2026 - published_year) DESC;+-----------------+----------------+ | title | published_year | +-----------------+----------------+ | Brave New World | 1932 | | Animal Farm | 1945 | | 1984 | 1949 | | Foundation | 1951 | | Dune | 1965 | +-----------------+----------------+
9. LIMIT and OFFSET
Limits the number of rows returned, and can be used with OFFSET to paginate results.
Return the first 3 rows:
SELECT *
FROM books
LIMIT 3;+----+-------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-------------+---------------+-----------+-------+----------------+----------+ | 1 | Dune | Frank Herbert | Sci-Fi | 15.50 | 1965 | 1 | | 2 | 1984 | George Orwell | Dystopian | 9.99 | 1949 | 1 | | 3 | Animal Farm | George Orwell | Satire | 7.50 | 1945 | 0 | +----+-------------+---------------+-----------+-------+----------------+----------+
Return the next 2 rows:
SELECT *
FROM books
LIMIT 2 OFFSET 3;+----+-----------------+---------------+-----------+-------+----------------+----------+ | id | title | author | genre | price | published_year | in_stock | +----+-----------------+---------------+-----------+-------+----------------+----------+ | 4 | Brave New World | Aldous Huxley | Dystopian | 10.25 | 1932 | 1 | | 5 | Foundation | Isaac Asimov | Sci-Fi | 12.00 | 1951 | 0 | +----+-----------------+---------------+-----------+-------+----------------+----------+
10. Putting it together
SELECT title, author, genre, price
FROM books
WHERE (genre = 'Sci-Fi' OR genre = 'Dystopian') AND in_stock = TRUE
ORDER BY price ASC
LIMIT 3;Read it like a sentence: "Give me title, author, genre, price from books where the genre is Sci-Fi or Dystopian, and it's in stock, sorted cheapest first, top 3 only."
+-----------------+---------------+-----------+-------+ | title | author | genre | price | +-----------------+---------------+-----------+-------+ | 1984 | George Orwell | Dystopian | 9.99 | | Brave New World | Aldous Huxley | Dystopian | 10.25 | | Dune | Frank Herbert | Sci-Fi | 15.50 | +-----------------+---------------+-----------+-------+
11. Quick Reference
| Goal | Example |
|---|---|
| Both conditions must be true | WHERE a AND b |
| Either condition can be true | WHERE a OR b |
| Mixing AND / OR safely | WHERE (a OR b) AND c |
| Invert a condition | WHERE NOT condition |
| Range check (inclusive) | WHERE price BETWEEN 10 AND 15 |
| Match against a list | WHERE author IN ('A', 'B') |
| Check for missing values | WHERE column IS NULL |
| Sort by multiple columns | ORDER BY genre ASC, price ASC |