Filtering in depth

The previous lesson introduced WHERE with the = operator. That handles simple cases like “find the user with id 1.” But real applications need much more flexible filtering. You need to search for patterns in text, check ranges of dates, handle missing values, and combine multiple conditions together.

Think about the features you see in any app. Search boxes. Date filters. “Show only users created this month.” “Find books matching this keyword.” All of those are WHERE clauses under the hood. This lesson covers every filtering tool you will use regularly.

Comparison operators

These are the building blocks. You have already seen =, but here is the full set:

-- Equal to
SELECT * FROM users WHERE id = 1;

-- Not equal to
SELECT * FROM users WHERE id != 1;

-- Greater than
SELECT * FROM users WHERE id > 5;

-- Less than
SELECT * FROM users WHERE id < 5;

-- Greater than or equal to
SELECT * FROM users WHERE id >= 5;

-- Less than or equal to
SELECT * FROM users WHERE id <= 5;

These work on numbers, text (alphabetical comparison), and dates stored as text in ISO 8601 format. The reason ISO 8601 works for text comparison is that dates like '2025-01-15' sort correctly when compared as strings. The year comes first, then the month, then the day, so alphabetical order matches chronological order.

LIKE for pattern matching

LIKE compares text against a pattern. It uses two wildcard characters:

• % matches any sequence of characters, including none at all.
• _ matches exactly one character.

-- Names starting with 'A'
SELECT * FROM users WHERE name LIKE 'A%';

-- Names ending with 'e'
SELECT * FROM users WHERE name LIKE '%e';

-- Names containing 'li'
SELECT * FROM users WHERE name LIKE '%li%';

-- Names that are exactly 3 characters long
SELECT * FROM users WHERE name LIKE '___';

In that last example, each _ stands for exactly one character. Three underscores means “any three characters.”

One thing to know. LIKE is case-insensitive for ASCII characters in SQLite. LIKE 'alice%' matches both 'Alice' and 'alice'. This is different from some other databases where LIKE is case-sensitive by default, so be careful when porting queries around.

IN for matching a set of values

IN checks whether a value is one of a specific list:

SELECT * FROM users WHERE id IN (1, 3, 5);

This returns rows where id is 1, 3, or 5. It is equivalent to writing id = 1 OR id = 3 OR id = 5, but much more readable when the list is long.

You can also use NOT IN to exclude specific values:

SELECT * FROM users WHERE id NOT IN (1, 3, 5);

This returns every row except those with id 1, 3, or 5.

BETWEEN for ranges

BETWEEN checks whether a value falls within a range. It is inclusive on both ends:

SELECT * FROM users WHERE id BETWEEN 10 AND 20;

This is equivalent to id >= 10 AND id <= 20. It works with numbers, text, and dates:

SELECT * FROM users
WHERE created_at BETWEEN '2025-01-01' AND '2025-03-31';

This finds all users created in the first quarter of 2025. Because our dates are stored as ISO 8601 text, the string comparison works correctly.

IS NULL and IS NOT NULL

Remember that NULL means “no value.” Here is something that trips up many beginners. You cannot check for NULL with =. The expression NULL = NULL does not evaluate to true. It evaluates to NULL, which the WHERE clause treats as false.

Instead, you use IS NULL and IS NOT NULL:

-- Users who have not set a bio
SELECT * FROM users WHERE bio IS NULL;

-- Users who have set a bio
SELECT * FROM users WHERE bio IS NOT NULL;

This is a common source of bugs. If you write WHERE bio = NULL, it will never match any rows, even if rows with NULL bios exist in the table. Always use IS NULL.

Combining conditions

You can build complex filters with AND, OR, and parentheses:

SELECT * FROM users
WHERE (name LIKE 'A%' OR name LIKE 'B%')
  AND created_at > '2025-01-01';

The parentheses control the order of evaluation, just like in math. Without them, AND binds more tightly than OR, which can produce unexpected results.

For example, without parentheses, name LIKE 'A%' OR name LIKE 'B%' AND created_at > '2025-01-01' would mean: “name starts with A (regardless of date), OR name starts with B AND was created after 2025.” That is probably not what you intended. The parentheses make your intent clear.

Checking existence

Sometimes you do not need the actual data. You just want to know: does a row matching this condition exist?

SELECT EXISTS(
  SELECT 1 FROM users WHERE email = '[email protected]'
);

This returns 1 (true) or 0 (false). The inner SELECT 1 is a convention. We do not care about the actual data, just whether at least one matching row exists. SQLite stops searching as soon as it finds the first match, so this is efficient even on large tables.

[!NOTE] Learn to read WHERE clauses left to right. Each condition narrows the result set further. Start with the most selective condition first for readability, though SQLite’s query planner handles performance optimization regardless of the order you write them in.

Exercises

Exercise 1: Write a query that finds all users whose name contains the letter “a” (case-insensitive). Then write one that finds users whose email ends with @example.com.

Exercise 2: Write a query using BETWEEN to find users created within a specific date range. Then rewrite it using >= and <= to verify they return the same results.

Exercise 3: Write SELECT * FROM users WHERE bio = NULL; and then SELECT * FROM users WHERE bio IS NULL;. Compare the results. The first returns nothing, even if NULL bios exist. Understand why.

We can now filter data with precision. But the results come back in no particular order, and sometimes we need summaries instead of raw rows. In the next lesson, we will learn how to sort, group, and aggregate data.