Skip to main content

🔍 Lesson 7: WHERE Clauses, Operators & Filtering

A database with a million rows is useless if you can't find the exact data you need. The WHERE clause is your search engine — it lets you filter rows by value, range, pattern, and logic. Master it and you master SQL.

🎯 Learning Objectives

By the end of this lesson, you will be able to:

  • Filter rows with comparison operators (=, !=, <, >, <=, >=)
  • Combine conditions with AND, OR, and NOT
  • Check membership with IN and NOT IN
  • Filter ranges with BETWEEN
  • Match patterns with LIKE and wildcards (%, _)
  • Handle missing data with IS NULL and IS NOT NULL
  • Understand operator precedence and use parentheses to control logic

Estimated Time: 45 minutes

Prerequisites: CRUD operations (Lesson 6)

📑 In This Lesson

Sample Data Setup

We'll continue with the bookstore database. Run this to ensure you have a good dataset to work with:


USE bookstore;

-- Clear and reload books
DELETE FROM books;
ALTER TABLE books AUTO_INCREMENT = 1;

INSERT INTO books (title, author, price, pages, published, in_stock) VALUES
    ('Dune', 'Frank Herbert', 14.99, 412, '1965-08-01', TRUE),
    ('1984', 'George Orwell', 9.99, 328, '1949-06-08', TRUE),
    ('To Kill a Mockingbird', 'Harper Lee', 11.99, 281, '1960-07-11', TRUE),
    ('The Great Gatsby', 'F. Scott Fitzgerald', 8.99, 180, '1925-04-10', FALSE),
    ('Pride and Prejudice', 'Jane Austen', 7.99, 432, '1813-01-28', TRUE),
    ('The Hobbit', 'J.R.R. Tolkien', 10.99, 310, '1937-09-21', TRUE),
    ('Brave New World', 'Aldous Huxley', 9.49, 311, '1932-01-01', TRUE),
    ('The Catcher in the Rye', 'J.D. Salinger', 8.99, 234, '1951-07-16', FALSE),
    ('Lord of the Rings', 'J.R.R. Tolkien', 15.99, 1178, '1954-07-29', TRUE),
    ('Fahrenheit 451', 'Ray Bradbury', 10.49, 158, '1953-10-19', TRUE);

-- Clear and reload customers
DELETE FROM customers;
ALTER TABLE customers AUTO_INCREMENT = 1;

INSERT INTO customers (first_name, last_name, email, city) VALUES
    ('Alice', 'Johnson', 'alice@email.com', 'New York'),
    ('Bob', 'Smith', 'bob@email.com', 'Chicago'),
    ('Carol', 'Williams', 'carol@email.com', 'Los Angeles'),
    ('Dave', 'Brown', 'dave@email.com', 'Houston'),
    ('Eve', 'Davis', 'eve@email.com', NULL),
    ('Frank', 'Miller', 'frank@email.com', 'New York'),
    ('Grace', 'Wilson', 'grace@email.com', 'Chicago'),
    ('Hank', 'Taylor', 'hank@email.com', NULL);

📖 Follow Along!

Open your MySQL CLI and run each query as you read. Experiment — change the values, combine operators, break things. That's how you learn.

Comparison Operators

Comparison operators compare a column's value against a target. They return TRUE or FALSE for each row, and only rows where the condition is TRUE appear in the results.

Operator Meaning Example
= Equal to WHERE price = 9.99
!= or <> Not equal to WHERE author != 'Unknown'
< Less than WHERE price < 10
> Greater than WHERE pages > 300
<= Less than or equal to WHERE price <= 9.99
>= Greater than or equal to WHERE pages >= 300

Equality: =


-- Find a specific book
SELECT * FROM books WHERE title = '1984';

-- Find a specific author's books
SELECT title, price FROM books WHERE author = 'J.R.R. Tolkien';

Output (Tolkien query):


+-------------------+-------+
| title             | price |
+-------------------+-------+
| The Hobbit        | 10.99 |
| Lord of the Rings | 15.99 |
+-------------------+-------+

💡 String Comparisons Are Case-Insensitive

By default (with utf8mb4_unicode_ci collation), MySQL string comparisons are case-insensitive. This means WHERE author = 'j.r.r. tolkien' matches 'J.R.R. Tolkien'. This is usually what you want, but be aware of it.

Inequality: != or <>


-- All books NOT by Tolkien
SELECT title, author FROM books WHERE author != 'J.R.R. Tolkien';

-- Same thing with <>
SELECT title, author FROM books WHERE author <> 'J.R.R. Tolkien';

Both != and <> do exactly the same thing. Use whichever feels more natural — != is more common in modern usage.

Greater / Less Than


-- Books under $10
SELECT title, price FROM books WHERE price < 10;

-- Books with more than 300 pages
SELECT title, pages FROM books WHERE pages > 300;

-- Books published on or after 1950
SELECT title, published FROM books WHERE published >= '1950-01-01';

Output (published >= 1950):


+-----------------------+------------+
| title                 | published  |
+-----------------------+------------+
| Dune                  | 1965-08-01 |
| To Kill a Mockingbird | 1960-07-11 |
| The Catcher in the Rye| 1951-07-16 |
| Lord of the Rings     | 1954-07-29 |
| Fahrenheit 451        | 1953-10-19 |
+-----------------------+------------+

💡 Comparing Dates

MySQL compares DATE values chronologically, so < means "before" and > means "after." This is one of the big reasons to store dates as DATE rather than VARCHAR — string comparison would give wrong results (e.g., "9" > "10" as strings).

AND, OR & NOT

Logical operators combine multiple conditions. They let you build complex filters by chaining simple ones together.

graph TD A["Logical Operators"] --> B["AND
Both conditions must be TRUE"] A --> C["OR
At least one must be TRUE"] A --> D["NOT
Reverses the condition"]

AND — Both Must Be True


-- Books under $10 AND more than 200 pages
SELECT title, price, pages
FROM books
WHERE price < 10 AND pages > 200;

Output:


+-------------------------+-------+-------+
| title                   | price | pages |
+-------------------------+-------+-------+
| 1984                    |  9.99 |   328 |
| Pride and Prejudice     |  7.99 |   432 |
| Brave New World         |  9.49 |   311 |
| The Catcher in the Rye  |  8.99 |   234 |
+-------------------------+-------+-------+
                    

-- In-stock books by Tolkien
SELECT title, price FROM books
WHERE author = 'J.R.R. Tolkien' AND in_stock = TRUE;

-- Customers in New York named Alice
SELECT * FROM customers
WHERE city = 'New York' AND first_name = 'Alice';

OR — At Least One Must Be True


-- Books by Orwell OR Huxley
SELECT title, author FROM books
WHERE author = 'George Orwell' OR author = 'Aldous Huxley';

Output:


+-----------------+---------------+
| title           | author        |
+-----------------+---------------+
| 1984            | George Orwell |
| Brave New World | Aldous Huxley |
+-----------------+---------------+
                    

-- Books that are cheap OR short
SELECT title, price, pages FROM books
WHERE price < 9 OR pages < 200;

-- Customers from New York or Chicago
SELECT first_name, last_name, city FROM customers
WHERE city = 'New York' OR city = 'Chicago';

NOT — Reverse the Condition


-- Books NOT in stock
SELECT title, in_stock FROM books
WHERE NOT in_stock;

-- Same result, different syntax:
SELECT title, in_stock FROM books
WHERE in_stock = FALSE;

Combining AND & OR

You can chain multiple operators, but be careful with precedence — AND is evaluated before OR:


-- ⚠️ This might not do what you expect:
SELECT title, author, price FROM books
WHERE author = 'J.R.R. Tolkien' OR author = 'George Orwell' AND price < 12;

Because AND binds tighter than OR, MySQL reads this as:


-- What MySQL actually evaluates:
WHERE author = 'J.R.R. Tolkien'
   OR (author = 'George Orwell' AND price < 12)

This returns ALL Tolkien books (regardless of price) plus Orwell books under $12. To get the intended meaning, use parentheses:


-- ✅ Use parentheses to control logic:
SELECT title, author, price FROM books
WHERE (author = 'J.R.R. Tolkien' OR author = 'George Orwell')
  AND price < 12;

✅ Best Practice: Always Use Parentheses

When mixing AND and OR, always use parentheses to make your intent explicit — even when they're technically not needed. It makes your SQL easier to read and prevents subtle bugs.

IN & NOT IN

IN checks if a value matches any item in a list. It's a cleaner alternative to chaining multiple OR conditions:


-- Instead of this:
SELECT title, author FROM books
WHERE author = 'George Orwell'
   OR author = 'Aldous Huxley'
   OR author = 'Ray Bradbury';

-- Use IN:
SELECT title, author FROM books
WHERE author IN ('George Orwell', 'Aldous Huxley', 'Ray Bradbury');

Output:


+-----------------+---------------+
| title           | author        |
+-----------------+---------------+
| 1984            | George Orwell |
| Brave New World | Aldous Huxley |
| Fahrenheit 451  | Ray Bradbury  |
+-----------------+---------------+
                    

-- Customers in specific cities
SELECT first_name, city FROM customers
WHERE city IN ('New York', 'Chicago', 'Houston');

-- Books with specific page counts
SELECT title, pages FROM books
WHERE pages IN (180, 310, 412);

NOT IN — Exclude Matches


-- All books NOT by these authors
SELECT title, author FROM books
WHERE author NOT IN ('J.R.R. Tolkien', 'Jane Austen');

⚠️ NOT IN and NULL

If the list in NOT IN contains a NULL, the result is always empty! This is because comparing anything to NULL yields UNKNOWN, not TRUE or FALSE. Avoid NULL in NOT IN lists:


-- ⛔ This returns NO rows (even though you'd expect some):
SELECT * FROM customers
WHERE city NOT IN ('New York', NULL);

-- ✅ Handle NULL separately:
SELECT * FROM customers
WHERE city NOT IN ('New York')
  AND city IS NOT NULL;

BETWEEN

BETWEEN filters values within an inclusive range — it's shorthand for >= AND <=:


-- Books priced between $9 and $12 (inclusive)
SELECT title, price FROM books
WHERE price BETWEEN 9 AND 12;

-- Same as:
SELECT title, price FROM books
WHERE price >= 9 AND price <= 12;

Output:


+-----------------------+-------+
| title                 | price |
+-----------------------+-------+
| 1984                  |  9.99 |
| To Kill a Mockingbird | 11.99 |
| The Hobbit            | 10.99 |
| Brave New World       |  9.49 |
| Fahrenheit 451        | 10.49 |
+-----------------------+-------+

BETWEEN with Dates

BETWEEN works beautifully with dates:


-- Books published in the 1950s
SELECT title, published FROM books
WHERE published BETWEEN '1950-01-01' AND '1959-12-31';

Output:


+-------------------------+------------+
| title                   | published  |
+-------------------------+------------+
| The Catcher in the Rye  | 1951-07-16 |
| Lord of the Rings       | 1954-07-29 |
| Fahrenheit 451          | 1953-10-19 |
+-------------------------+------------+
                    

-- Books published in the 20th century (1900-1999)
SELECT title, published FROM books
WHERE published BETWEEN '1900-01-01' AND '1999-12-31';

NOT BETWEEN


-- Books NOT in the $9–$12 range
SELECT title, price FROM books
WHERE price NOT BETWEEN 9 AND 12;

💡 BETWEEN Is Inclusive

BETWEEN 9 AND 12 includes both 9 and 12. If you want to exclude the endpoints, use > and < instead: WHERE price > 9 AND price < 12.

LIKE & Pattern Matching

LIKE searches for text patterns using two special wildcard characters:

Wildcard Matches Example Would Match
% Any number of characters (including zero) 'The%' "The Hobbit", "The Great Gatsby", "The"
_ Exactly one character '_984' "1984", but not "984" or "19984"

Starts With


-- Titles starting with "The"
SELECT title FROM books WHERE title LIKE 'The%';

Output:


+-------------------------+
| title                   |
+-------------------------+
| The Great Gatsby        |
| The Hobbit              |
| The Catcher in the Rye  |
+-------------------------+

Ends With


-- Authors whose last name ends with "ley"
SELECT DISTINCT author FROM books WHERE author LIKE '%ley';

Output:


+---------------+
| author        |
+---------------+
| Aldous Huxley |
| Ray Bradbury  |
+---------------+

Wait — "Bradbury" doesn't end in "ley"! Actually, looking at the data again, it doesn't match. Let's try a more precise example:


-- Titles ending with a specific word
SELECT title FROM books WHERE title LIKE '%Rye';
-- Returns: "The Catcher in the Rye"

Contains


-- Titles containing "the" anywhere
SELECT title FROM books WHERE title LIKE '%the%';

Output:


+-------------------------+
| title                   |
+-------------------------+
| The Great Gatsby        |
| The Hobbit              |
| The Catcher in the Rye  |
+-------------------------+

Notice "To Kill a Mockingbird" doesn't appear — it doesn't contain "the" (it has "a", not "the"). But "The Catcher in the Rye" matches twice — at the beginning and in the middle.

Single Character: _


-- Exactly 4-character titles
SELECT title FROM books WHERE title LIKE '____';
-- Matches "Dune" and "1984"

-- Customer names with exactly 3 letters
SELECT first_name FROM customers WHERE first_name LIKE '___';
-- Matches "Bob", "Eve"

Combining Wildcards


-- Author first name starts with any char, then "a"
SELECT DISTINCT author FROM books WHERE author LIKE '_a%';
-- Matches: "Jane Austen", "Ray Bradbury"

-- Emails with exactly 3 characters before @
SELECT first_name, email FROM customers WHERE email LIKE '___@%';
-- Matches: "Bob" (bob@email.com), "Eve" (eve@email.com)

NOT LIKE


-- Titles NOT starting with "The"
SELECT title FROM books WHERE title NOT LIKE 'The%';

💡 LIKE Performance

LIKE 'prefix%' (starts with) can use indexes and is fast. LIKE '%suffix' (ends with) and LIKE '%middle%' (contains) cannot use indexes and scan every row — they're slower on large tables. For full-text search on big datasets, MySQL offers FULLTEXT indexes (an advanced topic).

IS NULL & IS NOT NULL

NULL represents unknown or missing data. It's not zero, not an empty string, not "nothing" — it's unknown. And it requires special treatment.

graph LR A["NULL"] --> B["≠ 0"] A --> C["≠ ''
(empty string)"] A --> D["≠ FALSE"] A --> E["= Unknown /
Missing"]

The NULL Trap

You cannot use = or != to check for NULL — this is one of the most common SQL mistakes:


-- ⛔ WRONG — this returns NO rows, even though Eve and Hank have NULL cities!
SELECT * FROM customers WHERE city = NULL;

-- ⛔ ALSO WRONG:
SELECT * FROM customers WHERE city != NULL;

-- ✅ CORRECT — use IS NULL:
SELECT * FROM customers WHERE city IS NULL;

Output (IS NULL):


+----+------------+-----------+----------------+------+---------------------+
| id | first_name | last_name | email          | city | created_at          |
+----+------------+-----------+----------------+------+---------------------+
|  5 | Eve        | Davis     | eve@email.com  | NULL | 2026-04-16 ...      |
|  8 | Hank       | Taylor    | hank@email.com | NULL | 2026-04-16 ...      |
+----+------------+-----------+----------------+------+---------------------+

⚠️ Why = NULL Doesn't Work

In SQL, any comparison with NULL yields UNKNOWN — not TRUE or FALSE. Even NULL = NULL is UNKNOWN! Think of it this way: if you don't know Alice's city and you don't know Bob's city, you can't say their cities are the same. They're both unknown.

IS NOT NULL


-- Customers who DO have a city
SELECT first_name, city FROM customers WHERE city IS NOT NULL;

Output:


+------------+-------------+
| first_name | city        |
+------------+-------------+
| Alice      | New York    |
| Bob        | Chicago     |
| Carol      | Los Angeles |
| Dave       | Houston     |
| Frank      | New York    |
| Grace      | Chicago     |
+------------+-------------+

Combining NULL Checks with Other Conditions


-- Customers in New York OR with unknown city
SELECT first_name, city FROM customers
WHERE city = 'New York' OR city IS NULL;

-- Books with known page counts over 300
SELECT title, pages FROM books
WHERE pages IS NOT NULL AND pages > 300;

✅ NULL-Safe Equality: <=>

MySQL has a special operator <=> (spaceship operator) that treats NULL as a comparable value:


-- These two NULLs ARE considered equal:
SELECT * FROM customers WHERE city <=> NULL;
-- Same as: WHERE city IS NULL

-- Compare two potentially-NULL values safely:
-- WHERE a <=> b  (returns TRUE if both are NULL)

The <=> operator is rarely needed but useful when comparing columns that might both contain NULL.

Operator Precedence

When a WHERE clause has multiple operators, MySQL evaluates them in a specific order:

Priority Operator Evaluated
1 (highest) NOT First
2 AND Second
3 (lowest) OR Last

This means AND always binds tighter than OR. Let's see how this can bite you:


-- "Find cheap books by Orwell or Huxley"
-- ⛔ WRONG interpretation:
SELECT title, author, price FROM books
WHERE author = 'George Orwell' OR author = 'Aldous Huxley' AND price < 10;
-- MySQL reads: Orwell (any price) OR (Huxley AND price < 10)
-- Returns ALL Orwell books + only cheap Huxley books

-- ✅ CORRECT — parentheses force the OR to evaluate first:
SELECT title, author, price FROM books
WHERE (author = 'George Orwell' OR author = 'Aldous Huxley') AND price < 10;
-- Returns cheap books by either Orwell or Huxley
💡 Golden Rule: When mixing AND and OR, always use parentheses. Even when you know the precedence, parentheses make your intent clear to the next developer (including future you).

Complex Example


-- Find in-stock books that are either:
--   - under $10, OR
--   - by Tolkien
SELECT title, author, price, in_stock FROM books
WHERE in_stock = TRUE
  AND (price < 10 OR author = 'J.R.R. Tolkien');

Output:


+---------------------+----------------+-------+----------+
| title               | author         | price | in_stock |
+---------------------+----------------+-------+----------+
| 1984                | George Orwell  |  9.99 |        1 |
| Pride and Prejudice | Jane Austen    |  7.99 |        1 |
| The Hobbit          | J.R.R. Tolkien | 10.99 |        1 |
| Brave New World     | Aldous Huxley  |  9.49 |        1 |
| Lord of the Rings   | J.R.R. Tolkien | 15.99 |        1 |
+---------------------+----------------+-------+----------+

Exercises

🏋️ Exercise 1: Comparison Operators

Write queries to find:

  1. All books priced at exactly $9.99
  2. All books with more than 400 pages
  3. All books published before 1950
  4. All customers NOT in Houston
✅ Solution 

-- 1
SELECT * FROM books WHERE price = 9.99;

-- 2
SELECT title, pages FROM books WHERE pages > 400;

-- 3
SELECT title, published FROM books WHERE published < '1950-01-01';

-- 4
SELECT first_name, city FROM customers
WHERE city != 'Houston';
-- Note: this excludes NULL cities too!
-- To include NULL: WHERE city != 'Houston' OR city IS NULL

🏋️ Exercise 2: AND, OR, IN, BETWEEN

Write queries to find:

  1. In-stock books priced between $9 and $12
  2. Books by Tolkien, Orwell, or Huxley (use IN)
  3. Books published in the 1960s (1960–1969)
  4. Customers from New York or Chicago with first names starting before "F" alphabetically
✅ Solution 

-- 1
SELECT title, price, in_stock FROM books
WHERE in_stock = TRUE AND price BETWEEN 9 AND 12;

-- 2
SELECT title, author FROM books
WHERE author IN ('J.R.R. Tolkien', 'George Orwell', 'Aldous Huxley');

-- 3
SELECT title, published FROM books
WHERE published BETWEEN '1960-01-01' AND '1969-12-31';

-- 4
SELECT first_name, last_name, city FROM customers
WHERE city IN ('New York', 'Chicago')
  AND first_name < 'F';

🏋️ Exercise 3: LIKE & NULL

Write queries to find:

  1. All books with "the" in the title (anywhere)
  2. Authors whose names contain a period (.)
  3. Customers with 4-letter first names
  4. All customers whose city is unknown (NULL)
  5. All customers who have a known city that is NOT "New York"
✅ Solution 

-- 1
SELECT title FROM books WHERE title LIKE '%the%';

-- 2
SELECT DISTINCT author FROM books WHERE author LIKE '%.%';

-- 3
SELECT first_name FROM customers WHERE first_name LIKE '____';

-- 4
SELECT first_name, city FROM customers WHERE city IS NULL;

-- 5 (must exclude NULLs explicitly)
SELECT first_name, city FROM customers
WHERE city IS NOT NULL AND city != 'New York';

🏋️ Exercise 4: Complex Filters

Write a single query that finds all books that are:

  • Currently in stock, AND
  • EITHER priced under $10 OR published after 1960, AND
  • NOT by Jane Austen

Hint: Use parentheses carefully!

✅ Solution 

SELECT title, author, price, published, in_stock
FROM books
WHERE in_stock = TRUE
  AND (price < 10 OR published > '1960-12-31')
  AND author != 'Jane Austen';

🎯 Quick Quiz

Question 1: How do you check if a column contains NULL?

Question 2: What does the % wildcard match in a LIKE expression?

Question 3: Which operator has higher precedence — AND or OR?

Question 4: Is BETWEEN 5 AND 10 inclusive or exclusive of the endpoints?

Summary

🎉 Key Takeaways

  • Comparison operators (=, !=, <, >, <=, >=) filter rows by comparing column values
  • AND requires all conditions to be true; OR requires at least one; NOT reverses
  • IN checks membership in a list — cleaner than chaining OR
  • BETWEEN filters inclusive ranges — works great with numbers and dates
  • LIKE matches patterns: % = any characters, _ = one character
  • IS NULL / IS NOT NULL is the only way to check for NULL — never use = NULL
  • AND has higher precedence than OR — always use parentheses when mixing them
  • String comparisons are case-insensitive by default (with utf8mb4_unicode_ci)

WHERE Clause Quick Reference

Pattern Syntax
Exact match WHERE col = value
Not equal WHERE col != value
Range WHERE col BETWEEN a AND b
List membership WHERE col IN (a, b, c)
Starts with WHERE col LIKE 'prefix%'
Contains WHERE col LIKE '%text%'
Ends with WHERE col LIKE '%suffix'
Is missing WHERE col IS NULL
Is present WHERE col IS NOT NULL
Combined WHERE (a OR b) AND c

📚 Additional Resources

🚀 What's Next?

Now you can filter down to exactly the rows you need. But what about their order? And what if you only want the first 10 results? In the next lesson, you'll learn to sort with ORDER BY and paginate with LIMIT and OFFSET — essential tools for building real application features like search results and product listings.

🎉 Congratulations!

You've unlocked the power of filtering. No more wading through entire tables — now you can ask MySQL for exactly the rows you need, down to the finest detail.