Courses/SQL/WHERE Clause & Filtering

Lesson 4 • Beginner Track

WHERE Clause & Filtering

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.

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

Before you start: finish the SELECT Statement lesson so you're comfortable choosing columns and reading a result. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected result so you can check yourself.

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.

Result:

id	product_name	category	price	stock
1	Wireless Mouse	Electronics	24.99	120
2	Coffee Mug	Kitchen	9.5	300
3	Mechanical Keyboard	Electronics	79	45
4	Notebook	Stationery	3.25	500
5	Desk Lamp	Home	32	80
6	USB-C Cable	Electronics	12.99	200

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.

🚪 Real-world analogy

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 !=):

Operator	Meaning	Example
=	Equal to	category = 'Home'
<>	Not equal to	category <> 'Home'
>	Greater than	price > 30
<	Less than	stock < 100
>=	Greater than or equal	price >= 24.99
<=	Less than or equal	stock <= 80

Greater-than filter

Keep only products priced over $30.

Try it Yourself »

SQL

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

Result — 2 rows:

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.

Exact match with =

Find every Electronics product.

Try it Yourself »

SQL

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

Result — 3 rows:

product_name	category
Wireless Mouse	Electronics
Mechanical Keyboard	Electronics
USB-C Cable	Electronics

Not equal with <>

Everything that is NOT Electronics.

Try it Yourself »

SQL

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

Result — 3 rows:

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.

🎯 Your Turn: WHERE category = 'Electronics'

Replace the ___ blanks with the column and the value.

Try it Yourself »

SQL

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

AND, OR, NOT

Three queries showing each logical operator.

Try it Yourself »

SQL

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

Result — AND result: Electronics under $20 — 1 row:

product_name	category	price
USB-C Cable	Electronics	12.99

Order of operations: AND is evaluated before OR, just like × before + in maths. So A OR B AND C quietly means A OR (B AND C). Whenever you mix AND and OR, add parentheses to say exactly what you mean.

Grouping with parentheses

Electronics or Home, but only the cheap ones.

Try it Yourself »

SQL

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

Result — 2 rows:

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.

BETWEEN a low and high value

Products priced from $10 to $35.

Try it Yourself »

SQL

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

Result — 3 rows:

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.

🎯 Your Turn: WHERE price BETWEEN ... AND ...

Replace the ___ blanks with the low and high prices.

Try it Yourself »

SQL

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

IN (...) list match

Products in any of three categories.

Try it Yourself »

SQL

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

Result — 3 rows:

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.

Remember the wildcard: LIKE 'Mouse' matches only the exact word "Mouse". To find names that contain Mouse you need LIKE '%Mouse%'.

LIKE with % and _

Starts-with, ends-with and contains searches.

Try it Yourself »

SQL

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

Result — '%a%' result — names containing 'a':

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.

💡 Pro Tip

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

IS NULL / IS NOT NULL

Test for present vs. missing values the correct way.

Try it Yourself »

SQL

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

Result — IS NOT NULL — all 6 rows have a category:

product_name	category
Wireless Mouse	Electronics
Coffee Mug	Kitchen
…	…

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.

📘 Quick Reference

Syntax	What it matches
WHERE price > 30	Rows where the condition is true
= , <> , > , < , >= , <=	The six comparison operators
AND / OR / NOT	Combine or flip conditions (use parentheses!)
BETWEEN 10 AND 35	Inclusive range (10 and 35 both count)
IN ('A','B','C')	Equals any value in the list
LIKE '%Pro%'	Pattern: % = any chars, _ = one char
IS NULL / IS NOT NULL	Missing vs. present values

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.

🎯 Mini-Challenge

Combine an OR group, a BETWEEN range, and a LIKE pattern.

Try it Yourself »

SQL

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

WHERE Clause & Filtering

What You'll Learn

Our Sample Table: products

1. The WHERE Clause & Comparison Operators

Greater-than filter

Exact match with =

Not equal with <>

Your Turn: filter by category

🎯 Your Turn: WHERE category = 'Electronics'

2. Combining Conditions: AND, OR, NOT

AND — both must be true

OR — either can be true

NOT — flips a condition

AND, OR, NOT

Grouping with parentheses

3. BETWEEN — Match a Range

BETWEEN a low and high value

Your Turn: a price range

🎯 Your Turn: WHERE price BETWEEN ... AND ...

4. IN — Match Any Value in a List

IN (...) list match

5. LIKE — Match Text Patterns

LIKE with % and _

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

IS NULL / IS NOT NULL

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: Multi-Condition Filter

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `products`