SQL lesson 2 of 8

Filtering Rows in SQL with WHERE

Cut a SQL result down to the rows you mean: comparison operators, AND, OR and brackets, IN, BETWEEN, LIKE pattern matching, and why NULL makes rows vanish from a filter that looks correct.

Published 2026-09-16 · Every example on this page was run before it was published.

Every query in the first lesson returned every row in the table. Real questions are never like that. "Which tools are overdue?" "Which cost more than five pounds?" "Which have never been borrowed?" Each one is a request for some of the rows, and WHERE is the clause that says which.

Remember that every run in this playground starts from a brand-new empty database, so each example below builds its own table first. The examples share one invented tools table from a community tool library. due_back holds the date a borrowed tool is due, and is NULL when the tool is sitting on the shelf.

WHERE and the comparison operators

A WHERE clause is a test applied to each row on its own. Rows that pass it come back; rows that fail it do not. Nothing else about the query changes.

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER, due_back TEXT);
INSERT INTO tools (name, shelf, deposit, due_back) VALUES
  ('hedge trimmer',  'garden', 950,  '2026-04-02'),
  ('cordless drill', 'power',  1200, NULL),
  ('wheelbarrow',    'garden', 500,  '2026-03-28'),
  ('tile cutter',    'power',  1500, NULL),
  ('hand saw',       'hand',   350,  NULL),
  ('post driver',    'garden', 1100, '2026-04-11');

SELECT name, shelf, deposit FROM tools WHERE deposit > 1000;

Output

name           | shelf  | deposit
---------------+--------+--------
cordless drill | power  |    1200
tile cutter    | power  |    1500
post driver    | garden |    1100
(3 rows)

Three of the six rows passed the test. The operators available are the ones you would guess — =, <> (also spelled !=), <, <=, >, >= — with one spelling worth pointing out: equality in SQL is a single =, not the == of most programming languages. SQLite accepts == as well, which is a courtesy you should not lean on, because other databases reject it.

Comparing text works too, and this is where a difference between databases matters:

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
INSERT INTO tools (name, shelf) VALUES
  ('hedge trimmer', 'garden'),
  ('cordless drill', 'Power'),
  ('tile cutter', 'power');

SELECT name, shelf FROM tools WHERE shelf = 'power';

Output

name        | shelf
------------+------
tile cutter | power
(1 row)

Only the row spelled exactly 'power' came back: = on text is case-sensitive in SQLite, and in PostgreSQL. MySQL's default collation is case-insensitive, so the same query there would return the 'Power' row as well. When it matters, compare lower(shelf) = 'power' and stop depending on the server's collation.

AND, OR and the brackets you need

AND requires both tests to pass; OR requires at least one. Mixing them without brackets is one of the most reliable ways to get a wrong answer that looks right, because AND binds more tightly than OR — just as × binds more tightly than + in arithmetic.

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT name, shelf, deposit
FROM tools
WHERE shelf = 'garden' OR shelf = 'power' AND deposit > 1300;

SELECT name, shelf, deposit
FROM tools
WHERE (shelf = 'garden' OR shelf = 'power') AND deposit > 1300;

Output

name          | shelf  | deposit
--------------+--------+--------
hedge trimmer | garden |     950
wheelbarrow   | garden |     500
tile cutter   | power  |    1500
(3 rows)

name        | shelf | deposit
------------+-------+--------
tile cutter | power |    1500
(1 row)

Two queries, two tables, and the only difference is a pair of brackets. The first was read as "garden, or power-and-expensive", so both cheap garden tools came back. The second was read as "garden-or-power, and expensive", which is what the English sentence "garden or power tools over £13" actually means. Write the brackets even when you have worked out that you do not need them; the next person to read the query has not.

NOT flips a test, and <> is usually clearer than NOT (... = ...) for a single comparison.

IN and BETWEEN

A chain of ORs against the same column is common enough to have its own shorthand. IN takes a list; BETWEEN takes two bounds and includes both of them.

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT name, shelf FROM tools WHERE shelf IN ('garden', 'hand');

SELECT name, deposit FROM tools WHERE deposit BETWEEN 500 AND 1200;

Output

name          | shelf
--------------+-------
hedge trimmer | garden
wheelbarrow   | garden
hand saw      | hand
(3 rows)

name           | deposit
---------------+--------
hedge trimmer  |     950
cordless drill |    1200
wheelbarrow    |     500
(3 rows)

BETWEEN 500 AND 1200 kept the 500 and the 1200, which is the detail people forget. If you want the bounds excluded, write deposit > 500 AND deposit < 1200 and say so plainly. And note that BETWEEN is only sensible when the low bound really is lower: BETWEEN 1200 AND 500 is not an error, it simply matches nothing.

IN has a second, much more powerful form where the list comes from another query instead of being typed out. That is the subject of lesson 6.

LIKE: matching part of a value

LIKE compares text against a pattern with two wildcards: % stands for any run of characters, including none, and _ stands for exactly one character.

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO tools (name) VALUES
  ('hedge trimmer'),
  ('cordless drill'),
  ('hand saw'),
  ('hand plane'),
  ('Hammer'),
  ('tile cutter');

SELECT name FROM tools WHERE name LIKE 'hand%';

SELECT name FROM tools WHERE name LIKE '%er';

SELECT name FROM tools WHERE name LIKE 'h_nd%';

Output

name
----------
hand saw
hand plane
(2 rows)

name
-------------
hedge trimmer
Hammer
tile cutter
(3 rows)

name
----------
hand saw
hand plane
(2 rows)

Look closely at the middle result. '%er' matched Hammer with a capital H, because SQLite's LIKE is case-insensitive for plain ASCII letters, while = on the same column is case-sensitive. PostgreSQL does the opposite: LIKE there is case-sensitive and ILIKE is the case-insensitive version. MySQL follows its collation. So LIKE is the one operator whose case behaviour you should always check against the database you are actually using rather than assume.

To search for a literal % or _, name an escape character with ESCAPE:

SQL

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('50% worn'), ('blade_guard missing'), ('handle fine');

SELECT body FROM notes WHERE body LIKE '%!%%' ESCAPE '!';

Output

body
--------
50% worn
(1 row)

'%!%%' reads as: anything, then a real percent sign (because ! was declared the escape character), then anything.

NULL: the reason rows disappear

This is the section to reread. NULL means "no value recorded", and the rule that follows from it is that any comparison with NULL is neither true nor false — it is unknown, and WHERE only keeps rows whose test came out true.

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, due_back TEXT);
INSERT INTO tools (name, due_back) VALUES
  ('hedge trimmer', '2026-04-02'),
  ('cordless drill', NULL),
  ('wheelbarrow', '2026-03-28'),
  ('hand saw', NULL);

SELECT name FROM tools WHERE due_back = NULL;

SELECT name FROM tools WHERE due_back IS NULL;

SELECT name FROM tools WHERE due_back IS NOT NULL;

Output

name
----
(0 rows)

name
--------------
cordless drill
hand saw
(2 rows)

name
-------------
hedge trimmer
wheelbarrow
(2 rows)

The first query is the classic bug. It is not a syntax error, it returns no rows, and it returns no rows even though two of them plainly have no due_back. = NULL asks "is this unknown value equal to this other unknown value", and the honest answer is "unknown", so nothing passes. IS NULL and IS NOT NULL are the only tests that work, and between them they always cover every row.

The same trap hides inside filters that never mention NULL at all:

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950),
  ('donated ladder', NULL),
  ('hand saw', 350);

SELECT name, deposit FROM tools WHERE deposit > 500;

SELECT name, deposit FROM tools WHERE deposit <= 500;

SELECT name, deposit FROM tools WHERE deposit > 500 OR deposit IS NULL;

Output

name          | deposit
--------------+--------
hedge trimmer |     950
(1 row)

name     | deposit
---------+--------
hand saw |     350
(1 row)

name           | deposit
---------------+--------
hedge trimmer  |     950
donated ladder | NULL
(2 rows)

"Over 500" gives one row and "500 or under" gives one row, out of three rows. The ladder is in neither result, because an unknown deposit is not known to be over 500 and not known to be under it either. Two filters that look like opposites do not add up to the whole table as soon as one NULL is present. If the missing rows belong in your answer, say so with OR deposit IS NULL.

COALESCE is the usual fix when you would rather substitute a value than lose the row. It returns its first argument that is not NULL:

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950),
  ('donated ladder', NULL),
  ('hand saw', 350);

SELECT name, COALESCE(deposit, 0) AS deposit_or_zero
FROM tools
WHERE COALESCE(deposit, 0) <= 500;

Output

name           | deposit_or_zero
---------------+----------------
donated ladder |               0
hand saw       |             350
(2 rows)

Now the ladder counts as a zero deposit and appears. SQLite also has IFNULL(x, y) for the two-argument case, which is the same idea; COALESCE is the standard spelling and takes any number of arguments, so it is the one worth learning.

A worked example

One query, four conditions, brackets where they are needed:

SQL

CREATE TABLE seed_packets (
  id        INTEGER PRIMARY KEY,
  variety   TEXT,
  family    TEXT,
  packets   INTEGER,
  sow_after TEXT
);

INSERT INTO seed_packets (variety, family, packets, sow_after) VALUES
  ('Dusk Runner',  'bean',    14, '2026-04-15'),
  ('Ripple Chard', 'leaf',    3,  '2026-03-01'),
  ('Slow Pearl',   'onion',   0,  '2026-02-20'),
  ('Quiet Giant',  'squash',  9,  NULL),
  ('Fen Kale',     'leaf',    22, '2026-03-10'),
  ('Ember Chilli', 'pepper',  5,  '2026-05-01');

SELECT variety, family, packets, COALESCE(sow_after, 'any time') AS sow_after
FROM seed_packets
WHERE packets > 0
  AND (family IN ('leaf', 'bean') OR sow_after IS NULL)
  AND variety NOT LIKE '%kale%';

Output

variety      | family | packets | sow_after
-------------+--------+---------+-----------
Dusk Runner  | bean   |      14 | 2026-04-15
Ripple Chard | leaf   |       3 | 2026-03-01
Quiet Giant  | squash |       9 | any time
(3 rows)

Reading the filter one line at a time: packets > 0 drops the variety with nothing left in the drawer. The bracketed line keeps leaf and bean families plus anything with no sowing date at all — without the brackets, AND would have bound to the IN and the squash would have gone. NOT LIKE '%kale%' then drops one of the two leaf varieties, and matches the lowercase pattern against a capital K because LIKE ignores ASCII case in SQLite. COALESCE in the SELECT list keeps the squash's blank date readable in the output.

Common mistakes

NOT IN against a list that contains NULL

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
INSERT INTO tools (name, shelf) VALUES
  ('hedge trimmer', 'garden'),
  ('cordless drill', 'power'),
  ('hand saw', 'hand');

SELECT name FROM tools WHERE shelf NOT IN ('garden', NULL);

Output

name
----
(0 rows)

No rows, no error, and no warning. shelf NOT IN ('garden', NULL) means "shelf is not garden and shelf is not that unknown value", and the second half is never true, so no row can pass. This is not a SQLite oddity — it is how NULL works everywhere, and it is why NOT IN (SELECT ...) against a column that allows NULL is a well-known way to lose an entire result set. Filter the NULLs out of the list first.

Writing a filter as a single string

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('hedge trimmer', 950), ('hand saw', 350);
SELECT name FROM tools WHERE 'deposit > 500';

Output

name
----
(0 rows)

The quotes turned the condition into a piece of text, and SQLite decides how true a piece of text is by reading a number off the front of it — 'deposit > 500' starts with a letter, so it counts as 0, which is false, so every row was dropped. A stray pair of quotes around a whole condition therefore produces an empty result rather than a complaint. PostgreSQL rejects the same query, because it insists a WHERE clause be a boolean.

Expecting a date column to behave like a date

SQL

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, due_back TEXT);
INSERT INTO tools (name, due_back) VALUES
  ('hedge trimmer', '2026-04-02'),
  ('wheelbarrow', '2026-03-28'),
  ('post driver', '02/04/2026');

SELECT name, due_back FROM tools WHERE due_back < '2026-04-01';

Output

name        | due_back
------------+-----------
wheelbarrow | 2026-03-28
post driver | 02/04/2026
(2 rows)

SQLite has no date or time type at all: a date is just text, a number of seconds, or a Julian day, and it is your job to keep one format. Comparing '2026-03-28' < '2026-04-01' works only because YYYY-MM-DD text sorts in the same order as the dates it names. The post driver row holds the same day as the hedge trimmer written the other way round, and it slipped through the filter because '0' sorts before '2'. PostgreSQL and MySQL both have real date types that would have rejected the mixed format on the way in. Store dates as YYYY-MM-DD here and use SQLite's date() and strftime() functions when you need arithmetic on them.

Next steps

You can now ask for the rows you mean and, just as importantly, spot the rows a filter quietly dropped. The result still arrives in whatever order the database found convenient, though — next comes sorting and limiting, which puts rows in a deliberate order, takes the top few, and removes duplicates. Try the worked example in the SQL playground first, and delete the brackets to watch the answer change.

Write it yourself

Reading about code and writing it are different skills. These exercises practise exactly what this lesson covered; they run in this tab and need no account.

Pick the Right RowsA shortlist of the cheap garden tools in a lending library, produced by tightening one condition in a WHERE clause that currently returns most of the table.

Open the SQL playground