Filtering Rows with WHERE

Introduction

Real tables have thousands of rows, and you rarely want all of them. WHERE is a filter: only rows that pass your test come through.

WHERE with a comparison

The comparison operators are =, <> (not equal, != also works), <, <=, > and >=. Note that SQL uses a single = to compare.

Text must be in single quotes:

AND, OR and NOT

• AND: both conditions must be true.
• OR: at least one must be true.
• NOT: flips a condition.

When you mix AND with OR, use brackets to be clear. AND is applied before OR, which often surprises beginners.

IN, BETWEEN and LIKE

Three shortcuts make filters shorter.

LIKE matches patterns in text. % stands for any number of characters, and _ for exactly one character.

In SQLite, LIKE ignores upper and lower case for plain English letters.

NULL: the missing value

NULL means "no value". One customer, Farid, has no city stored. You cannot test for it with =, because NULL is not equal to anything, not even itself. Use IS NULL and IS NOT NULL.

Try changing IS NULL to = NULL and run it again. It returns nothing, which is a classic beginner mistake.

Try it yourself

Write a query that lists the name and price of every product that is not in the Stationery category and costs more than 10.

Summary

• WHERE keeps only rows that pass a test.
• Combine tests with AND, OR and NOT, and use brackets when mixing them.
• IN checks a list, BETWEEN checks a range (both ends included), LIKE matches patterns with % and _.
• Test for missing values with IS NULL, never with =.