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. 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 |