SQL WHERE: Filter Rows With Conditions

You will add a WHERE clause that keeps only the rows matching a test, for text, numbers and dates.

What does WHERE do to a query?

WHERE keeps the rows that pass a test and drops the rest. The database reads every row from the table in FROM, checks the condition for that row, and only passes the row on if the answer is true. SELECT then picks the columns from the rows that survived.

This lesson uses the repairs book from a bike shop. Each row is one booked job: the customer, the kind of bike, the work, the price in pounds, the status and the date it was booked. Prices are stored as decimal numbers, so 12.5 means £12.50.

Open the Rows view after you run the query below. You will see the FROM step with all ten rows, then the WHERE step with the rows that failed crossed out and a note on why, then the SELECT step trimming the columns.

SELECT customer, job, price
FROM repairs
WHERE price > 50;

The ten bike shop repairs tested one by one against booked_on >= 2026-03-15. The six earlier bookings are faded out as false, and four repairs from 15 March onwards pass into the result. — `WHERE` checks the condition once per row and passes on only the rows where it is true. The two bookings on 15 March stay, because `>=` includes the boundary date.

Which comparison operators can you use?

SQL has six comparison operators. Each one compares the value on the left with the value on the right and gives true or false for that row. Note the single = for equals; SQLite also accepts ==, but other databases do not, so stick to one.

<> and != both mean "not equal". <> is the standard form that every database understands, and you will see both in real code.

SELECT id, customer, status
FROM repairs
WHERE status <> 'done';

= equal to: status = 'done'
<> or != not equal to: status <> 'done'
< less than: price < 20
> greater than: price > 50
<= less than or equal to: price <= 35
>= greater than or equal to: booked_on >= '2026-03-15'

How do you filter on text?

Text values go inside single quotes: 'Hybrid', 'Full service'. Without quotes, the database thinks you mean a column name and reports "no such column". With double quotes, SQLite first looks for a column with that name and only then treats it as text, which is a trap: the day someone adds a column with that name, your filter changes meaning without any error. Use single quotes for text, always.

Comparing text with = is exact. Capital letters count, so 'Hybrid' does not match 'hybrid'. Spaces count too. This table has one awkward row: repair 7 was saved with status 'Waiting' while the others say 'waiting'. Run the query below and it is missing. Messy capitals like this are common in data typed by people, and the lesson on searching text shows how to match regardless of case.

If the text itself contains an apostrophe, write it twice: 'Owen''s bike'. The doubled quote stands for one apostrophe inside the value.

SELECT id, customer, status
FROM repairs
WHERE status = 'waiting';

How do you filter on numbers?

Numbers are written without quotes: price < 30, id = 4. The comparison is numeric, so 9.5 < 12 is true, which is what you expect.

Write the number the same way the column stores it. price is in pounds here, so £25 is 25, and £12.50 is 12.5 or 12.50. Another shop might store prices as whole pence in an integer column, where the same filter would be price_pence < 2500. The schema tab tells you which you have.

Putting quotes around a number usually still works in SQLite, because it converts the text when the column holds numbers. Leave the quotes off anyway: it says plainly that you mean a number, and other databases are stricter.

SELECT customer, job, price
FROM repairs
WHERE price <= 28;

How do you compare dates stored as text?

SQLite has no separate date type. The usual way to store a date is as text in the form YYYY-MM-DD, like '2026-03-15'. That format has a useful property: sorting it alphabetically gives the same order as sorting by date, because the year comes first, then the month, then the day, all with a fixed number of digits.

So the ordinary comparison operators work on dates. booked_on < '2026-03-01' means "booked before March". booked_on >= '2026-03-15' means "on or after 15 March". Remember the quotes, since the date is text. Without them, 2026-03-15 is read as arithmetic, 2026 minus 3 minus 15, which is 2008, and the comparison silently gives nonsense.

The trick only works if every date uses the same format. A row written as '15/03/2026' or '2026-3-5' compares in the wrong place. If you inherit a table like that, fix the data first.

SELECT id, customer, booked_on
FROM repairs
WHERE booked_on < '2026-03-01';

Where does the boundary go: < or <=?

Most filtering bugs sit at the edge. "Repairs from 15 March" usually includes 15 March, so you want >=. "Jobs under £50" usually excludes £50 itself, so < 50. When the wording is vague, ask, or pick one and say which in a comment.

Test the edge with real data. This table has two repairs booked on exactly '2026-03-15'. Try the task with > and then >= and watch them leave and return. Building a test row that sits right on the boundary is the quickest way to find out what a query really does.

SELECT id, booked_on
FROM repairs
WHERE booked_on > '2026-03-15';

Can you filter on a column you do not show?

Yes. WHERE runs before SELECT, so it can test any column in the table, whether or not you list it. The query below filters on bike and shows only the customer and job.

Showing the filter column while you write a query is still a good idea, because you can eyeball the result and confirm every row really matches. Remove it once you trust the condition.

SELECT customer, job
FROM repairs
WHERE bike = 'Hybrid';

What should you check in a WHERE clause an AI wrote?

Read the condition as a sentence and compare it to the question you asked. Check the operator at the boundary, > against >=. Check that text is in single quotes and spelt the way the data spells it, including capitals. Check that dates are quoted and match the stored format.

Then compare row counts. If you expected about five rows and got zero, the condition probably compares against a value that never appears. Open the Tables view, find a row you know should match, and see what is really stored there.

Field notes

WHERE checks each row and keeps only those where the condition is true.
Put text in single quotes and write numbers without quotes.
Dates stored as YYYY-MM-DD text compare correctly with <, > and =.
Check the edge: "from 15 March" usually means >=, which includes that day.

Easy to get wrong

Without quotes, Hybrid is read as a column name, so the database reports that the column does not exist.

Unquoted, the date is a subtraction that equals 2008. Every text date then compares as greater than that number, so the filter keeps every row and no error warns you.

Double quotes mark names of tables and columns. SQLite guesses you meant text when no column matches, but PostgreSQL refuses, and a future column called done would change the meaning.

= compares text exactly, including capitals. Look at the stored values before you write the condition.

Questions people ask

Should I use <> or !=?

Both work in SQLite, PostgreSQL and MySQL. <> is the form in the SQL standard, so it is the safer habit if you move between databases.

Is `=` case-sensitive in every database?

No. SQLite and PostgreSQL compare text case-sensitively by default. MySQL usually does not, because its common default settings ignore case. Check before you rely on either behaviour.

Can WHERE use a column alias from SELECT?

Not in standard SQL, because WHERE runs before SELECT names anything. SQLite allows it as an extension, but repeating the expression is portable.

What if a value is missing?

A missing value is NULL, and comparisons with it are neither true nor false, so WHERE drops those rows. The lesson on missing values covers IS NULL.

Do I need an index for WHERE to work?

No. Without an index the database checks every row, which is fine for small tables. Indexes make filtering faster on large ones and get their own lesson.

Filtering rows