WHERE Clause & Filtering

Reviewed & published by Brayan K

In the last lesson you chose which columns to return. Now you'll choose which rows. By the end of this lesson you'll filter any table with WHERE — comparing values, combining conditions with AND/OR/NOT, matching ranges and lists, finding text patterns, and handling missing data correctly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Filter rows with the six comparison operators
• Combine conditions with AND, OR and NOT
• Group logic correctly with parentheses
• Match a range with BETWEEN and a list with IN
• Find text patterns with LIKE and % / _ wildcards
• Handle missing data with IS NULL / IS NOT NULL

Our Sample Table: products

This is the same products table from the SELECT lesson, so the results stay consistent. Every query below filters these six rows — keep them in front of you as you read.

1. The WHERE Clause & Comparison Operators

A WHERE clause adds a condition to a query. The database checks that condition once for each row and keeps only the rows where it is true. Everything else is filtered out.

WHERE is the bouncer at a club. Each row walks up; if it meets the rule (price > 30), it gets in; if not, it's turned away. The table itself is never changed — you're just choosing who gets through for this one query.

There are six comparison operators. Note that "not equal to" is written <> in standard SQL (most databases also accept !=):

-- The table this lesson uses. Run this block first, then every query
-- below filters these same six rows.
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT    NOT NULL,
    category     TEXT,               -- nullable, so IS NULL has something to teach
    price        REAL    NOT NULL,
    stock        INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
  (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
  (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
  (4, 'Notebook',            'Stationery',   3.25, 500),
  (5, 'Desk Lamp',           'Home',        32.00,  80),
  (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- WHERE keeps only the rows where the condition is TRUE
SELECT * FROM products
WHERE price > 30;

-- "price > 30" is checked once per row.
-- Mechanical Keyboard (79) and Desk Lamp (32) pass; the rest are dropped.

-- ✅ Expected result:
-- id | product_name | category | price | stock
-- 3 | Mechanical Keyboard | Electronics | 79 | 45
-- 5 | Desk Lamp | Home | 32 | 80

When you compare against text, wrap the value in single quotes ('Electronics'). Numbers like 30 are written bare, with no quotes.

-- Uses the products table from the first example.
-- = matches an exact value. Text goes in 'single quotes'.
SELECT product_name, category
FROM products
WHERE category = 'Electronics';

-- Three rows in our table are Electronics, so three rows come back.

-- ✅ Expected result:
-- product_name | category
-- Wireless Mouse | Electronics
-- Mechanical Keyboard | Electronics
-- USB-C Cable | Electronics

-- Uses the products table from the first example.
-- <> means "not equal to" (you may also see != in many databases)
SELECT product_name, category
FROM products
WHERE category <> 'Electronics';

-- Every row whose category is NOT Electronics passes the check.

-- ✅ Expected result:
-- product_name | category
-- Coffee Mug | Kitchen
-- Notebook | Stationery
-- Desk Lamp | Home

Your Turn: filter by category

Fill in the blanks to list the name and price of every Electronics product. Remember: column name on the left, the text value in single quotes on the right. The expected result is in the comments so you can check yourself.

-- Uses the products table from the first example.
-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: list the name and price of every Electronics product.

SELECT product_name, price
FROM products
WHERE ___ = ___;     -- 👉 column on the left (category), the text value on the right in 'quotes'

-- ✅ Expected result (3 rows):
--    Wireless Mouse | 24.99
--    Mechanical Keyboard | 79.00
--    USB-C Cable | 12.99

2. Combining Conditions: AND, OR, NOT

One condition is rarely enough. Logical operators let you chain several together:

AND — both must be true

"Electronics and under $20" → narrows the result down.

OR — either can be true

"Kitchen or Home" → widens the result out.

NOT — flips a condition

NOT stock > 100 is the same as stock <= 100.

-- Uses the products table from the first example.
-- AND: BOTH conditions must be true
SELECT product_name, category, price
FROM products
WHERE category = 'Electronics' AND price < 20;

-- OR: EITHER condition can be true
SELECT product_name, category
FROM products
WHERE category = 'Kitchen' OR category = 'Home';

-- NOT: flips a condition to its opposite
SELECT product_name, stock
FROM products
WHERE NOT stock > 100;   -- the same as stock <= 100

-- ✅ Expected result:
-- product_name | stock
-- Mechanical Keyboard | 45
-- Desk Lamp | 80

-- Uses the products table from the first example.
-- AND binds tighter than OR, so ALWAYS use parentheses when you mix them.
-- "Electronics OR Home, but only if it's under $30":
SELECT product_name, category, price
FROM products
WHERE (category = 'Electronics' OR category = 'Home')
  AND price < 30;

-- Without the brackets, "A OR B AND C" secretly means "A OR (B AND C)" —
-- usually NOT what you meant.

-- ✅ Expected result:
-- product_name | category | price
-- Wireless Mouse | Electronics | 24.99
-- USB-C Cable | Electronics | 12.99

3. BETWEEN — Match a Range

BETWEEN x AND y is shorthand for column >= x AND column <= y. It is inclusive: both end values count as matches. It reads cleanly and is perfect for prices, dates, and ages.

-- Uses the products table from the first example.
-- BETWEEN x AND y is an inclusive range: x, y, and everything between.
SELECT product_name, price
FROM products
WHERE price BETWEEN 10 AND 35;

-- Both ends count, so 10.00 and 35.00 would match if they existed.

-- ✅ Expected result:
-- product_name | price
-- Wireless Mouse | 24.99
-- Desk Lamp | 32
-- USB-C Cable | 12.99

Your Turn: a price range

Fill in the two range values so the query returns products priced from $5 up to $20 (inclusive). Low value first, then the high value.

-- Uses the products table from the first example.
-- 🎯 YOUR TURN — fill in the two range values.
-- Goal: list products priced from $5 up to $20 (inclusive).

SELECT product_name, price
FROM products
WHERE price BETWEEN ___ AND ___;   -- 👉 the low value, then the high value

-- ✅ Expected result (2 rows):
--    Coffee Mug | 9.50
--    USB-C Cable | 12.99

4. IN — Match Any Value in a List

IN (a, b, c) is true when the column equals any value in the list. It replaces a long chain of ORs and is much easier to read.

-- Uses the products table from the first example.
-- IN (...) matches ANY value in a list — a tidy shorthand for lots of ORs.
SELECT product_name, category
FROM products
WHERE category IN ('Kitchen', 'Stationery', 'Home');

-- Same result as:
--   category = 'Kitchen' OR category = 'Stationery' OR category = 'Home'

-- ✅ Expected result:
-- product_name | category
-- Coffee Mug | Kitchen
-- Notebook | Stationery
-- Desk Lamp | Home

5. LIKE — Match Text Patterns

LIKE searches text with two wildcards: % stands for any number of characters (including none), and _ stands for exactly one character. Use it for "starts with", "ends with", and "contains" searches.

-- Uses the products table from the first example.
-- LIKE matches a pattern. Two wildcards:
--   %  = any number of characters (including zero)
--   _  = exactly one character
SELECT product_name
FROM products
WHERE product_name LIKE '%Mouse';   -- ENDS with "Mouse"

SELECT product_name
FROM products
WHERE product_name LIKE 'C%';        -- STARTS with "C"

SELECT product_name
FROM products
WHERE product_name LIKE '%a%';       -- CONTAINS the letter "a" somewhere

-- ✅ Expected output:
-- product_name
-- Mechanical Keyboard
-- Desk Lamp
-- USB-C Cable

-- ✅ Expected result:
-- product_name
-- Mechanical Keyboard
-- Desk Lamp
-- USB-C Cable

6. IS NULL & IS NOT NULL — Handle Missing Data

NULL is SQL's way of saying "there is no value here" — unknown or missing. Because NULL is not really a value, you cannot test it with = or <>; those always come back "unknown" and match nothing. Use IS NULL and IS NOT NULL instead.

Never write WHERE category = NULL — it silently returns zero rows. It's always WHERE category IS NULL.

-- Uses the products table from the first example.
-- NULL means "no value / unknown". You CANNOT test it with = or <>.
-- Use IS NULL and IS NOT NULL instead.
SELECT product_name, category
FROM products
WHERE category IS NOT NULL;   -- rows that DO have a category

SELECT product_name
FROM products
WHERE category IS NULL;       -- rows missing a category (none in our table → 0 rows)

-- ✅ Expected output:
-- product_name

-- ✅ Expected result:
-- product_name

Common Errors (and the fix)

• Comparing to NULL with =: WHERE category = NULL returns 0 rows with no error — the trap is that it looks fine. Use IS NULL / IS NOT NULL.
• Wrong case or missing quotes: WHERE category = electronics errors ("no such column"), and = 'electronics' matches nothing because the data is stored as 'Electronics'. Text needs single quotes and the case must match the data.
• Mixing AND/OR without parentheses: WHERE category = 'Electronics' OR category = 'Home' AND price < 30 isn't grouped how you'd expect. Wrap the OR: WHERE (category = 'Electronics' OR category = 'Home') AND price < 30.
• LIKE without a wildcard: WHERE product_name LIKE 'Mouse' only matches the exact word. To search inside text use %: LIKE '%Mouse%'.
• Single vs. double quotes: use single quotes for text values ('Home'). Double quotes mean a column/identifier name in most databases, so "Home" is read as a column and errors.

Frequently Asked Questions

Q: What's the difference between <> and !=?

They do the same thing — "not equal to". <> is the official SQL standard; != works in most databases too. Pick one and stay consistent.

Q: Is BETWEEN inclusive or exclusive?

Inclusive. price BETWEEN 10 AND 35 includes both 10 and 35. If you want to exclude an end, use plain comparisons like price > 10 AND price < 35.

Q: Is LIKE case-sensitive?

It depends on the database. SQLite and MySQL are usually case-insensitive for ASCII letters; PostgreSQL's LIKE is case-sensitive (use ILIKE there). To be safe, match the case of your data.

Q: Why does WHERE category = NULL return nothing?

NULL means "unknown", and comparing anything to "unknown" gives "unknown" — never true. That's why you need IS NULL instead of =.

Mini-Challenge: Multi-Condition Filter

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

-- Uses the products table from the first example.
-- 🎯 MINI-CHALLENGE
-- Using ONLY what this lesson covered (WHERE, AND/OR, BETWEEN, IN, LIKE):
--   Find products that are:
--     • in the Electronics OR Home category, AND
--     • priced BETWEEN 20 and 40 (inclusive), AND
--     • whose name contains the letter "e" (case as stored)
--   Remember the parentheses around the OR!
--
-- ✅ Expected result (2 rows):
--    Wireless Mouse | Electronics | 24.99
--    Desk Lamp | Home | 32.00

-- your query here

🎉 Lesson Complete

• ✅ WHERE keeps only the rows where its condition is true
• ✅ Six comparison operators: =, <>, >, <, >=, <=
• ✅ AND/OR/NOT combine conditions — parenthesise when you mix them
• ✅ BETWEEN matches an inclusive range; IN matches a list
• ✅ LIKE with %/_ matches text patterns; IS NULL handles missing data
• ✅ Next: ORDER BY — sort your filtered rows into a meaningful order

Practice quiz

Continue this course

• Previous: SELECT Statement
• Next: ORDER BY & Sorting — Sort query results in ascending or descending order with ORDER BY
• Quick reference: SQL cheat sheet › Querying Data