SQLite WHERE Clause: Filter Rows with Conditions, LIKE, IN, BETWEEN

WHERE Filters Rows One at a Time

SELECT without WHERE returns every row in the table. That's rarely what you want. WHERE lets you keep only the rows that match a condition — SQLite walks the table, evaluates the condition against each row, and keeps the ones where it's true.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, year FROM books WHERE year > 1980;

Three rows come back: Neuromancer, Hyperion, and The Martian. The condition year > 1980 was tested against each row, and only the matches survived.

The mental model: WHERE is a filter sitting between FROM and the columns you select. Anything that evaluates to true passes through.

Comparison Operators

The basics work the way you'd expect:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, pages FROM books WHERE pages >= 400;
SELECT title FROM books WHERE author = 'Asimov';
SELECT title, year FROM books WHERE year != 1965;

= for equality, != or <> for "not equal," and <, <=, >, >= for ordering. String comparisons use the same operators — author = 'Asimov' matches exactly, character for character.

One thing to know: SQL uses single quotes for string literals. Double quotes are for identifiers (column or table names). WHERE author = "Asimov" might work in SQLite for historical reasons, but it's not portable and can silently misbehave when the "string" happens to match a column name. Stick with single quotes.

AND, OR, and Parentheses

Real queries usually combine conditions. AND requires both sides to be true; OR requires at least one:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books
WHERE year > 1980 AND pages < 400;

SELECT title FROM books
WHERE author = 'Herbert' OR author = 'Asimov';

The first query filters for recent and short books. The second pulls books by either author.

When you mix AND and OR, precedence bites people. AND binds tighter than OR, so:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books
WHERE author = 'Herbert' OR author = 'Gibson' AND year > 1980;

reads as Herbert OR (Gibson AND year > 1980) — every Herbert book regardless of year, plus Gibson books after 1980. Probably not what you meant. Wrap your intent in parentheses:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books
WHERE (author = 'Herbert' OR author = 'Gibson') AND year > 1980;

When in doubt, parenthesize. The query optimizer doesn't care, and the next person reading it will thank you.

NULL Doesn't Behave Like a Value

This is the WHERE-clause trap that catches everyone once. NULL in SQL means "unknown," and unknowns can't be compared. column = NULL is not false — it's NULL, which WHERE treats as "skip this row."

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Untitled Draft', 'Anonymous', NULL, 120);

-- Returns nothing, even though we have a NULL year:
SELECT title FROM books WHERE year = NULL;

-- This is the right way:
SELECT title FROM books WHERE year IS NULL;
SELECT title FROM books WHERE year IS NOT NULL;

IS NULL and IS NOT NULL are the only operators that test NULL directly. Burn those into your fingers — every other comparison with NULL returns NULL and silently drops rows.

The same rule applies to negation. WHERE author != 'Asimov' does not return rows where author IS NULL, because NULL != 'Asimov' is also NULL. If you want NULLs included, ask for them explicitly: WHERE author != 'Asimov' OR author IS NULL.

IN and BETWEEN: Shortcuts You'll Use Daily

IN checks membership in a list. It's a cleaner way to write a chain of ORs:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, author FROM books
WHERE author IN ('Herbert', 'Asimov', 'Weir');

SELECT title FROM books
WHERE year NOT IN (1965, 1984);

BETWEEN checks a range, inclusive on both ends:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, year FROM books
WHERE year BETWEEN 1980 AND 2000;

SELECT title, pages FROM books
WHERE pages NOT BETWEEN 300 AND 500;

year BETWEEN 1980 AND 2000 is identical to year >= 1980 AND year <= 2000 — just shorter. One thing to remember: both bounds are included. If you want exclusive bounds, write the comparisons out.

A quick note about IN and NULL: WHERE column NOT IN (1, 2, NULL) will return no rows ever, because comparing anything to NULL yields NULL. Filter out NULLs from your list, or handle them with IS NULL separately.

LIKE for Pattern Matching

LIKE does string pattern matching with two wildcards:

• % matches any sequence of characters (including none).
• _ matches exactly one character.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

-- Titles starting with "The":
SELECT title FROM books WHERE title LIKE 'The %';

-- Titles containing "ion" anywhere:
SELECT title FROM books WHERE title LIKE '%ion%';

-- Four-letter author names:
SELECT author FROM books WHERE author LIKE '____';

By default, SQLite's LIKE is case-insensitive for ASCII letters — 'Dune' LIKE 'dune' is true. That's surprising if you're coming from Postgres, where LIKE is case-sensitive and ILIKE is the case-insensitive version. (SQLite has no ILIKE.)

If you need case-sensitive matching, you have two options. Toggle the global pragma:

PRAGMA case_sensitive_like = ON;

Or use GLOB, which is always case-sensitive and uses Unix-style wildcards (* for any sequence, ? for one character):

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books WHERE title GLOB 'The *';
SELECT title FROM books WHERE title GLOB 'D*';

GLOB 'd*' would match nothing here — case matters.

Filtering Dates

SQLite stores dates as text (typically YYYY-MM-DD or full ISO 8601), which means string comparisons happen to also work as date comparisons — as long as you stick to the ISO format:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT,
    occurred_on TEXT
);

INSERT INTO events (name, occurred_on) VALUES
    ('Launch', '2024-01-15'),
    ('Patch', '2024-03-22'),
    ('Release', '2024-11-08'),
    ('Hotfix', '2025-02-01');

SELECT name FROM events
WHERE occurred_on >= '2024-06-01';

SELECT name FROM events
WHERE occurred_on BETWEEN '2024-01-01' AND '2024-12-31';

Because '2024-06-01' < '2024-11-08' is true both as strings and as dates, these queries do what you'd expect. If you store dates in any other format ('15/01/2024', 'Jan 15 2024'), comparisons will silently produce wrong results. Always use ISO 8601 — your future self will be happy.

For trickier date math (extracting the year, comparing to "today"), SQLite has the date(), strftime(), and julianday() functions. We'll cover those in the date-and-time chapter.

Putting It Together

A query that uses several of these at once:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369),
    ('Untitled Draft', 'Anonymous', NULL, 120);

SELECT title, author, year, pages
FROM books
WHERE year IS NOT NULL
  AND year BETWEEN 1950 AND 2000
  AND (author IN ('Herbert', 'Gibson') OR pages > 400)
  AND title NOT LIKE '%Draft%';

Read it line by line: keep rows with a known year, in range, by one of two authors or long enough, and not a draft. That's the WHERE clause doing what it does best — combining small, readable conditions into precise filters.

Two habits worth keeping:

• Indent each condition on its own line. Long WHERE clauses get unreadable fast as one giant line.
• Comment the intent where the condition isn't obvious. -- exclude drafts is cheap insurance.

Next: Operators and NULL in Detail

The WHERE clause is mostly operators applied to columns, and NULL quietly changes how every operator behaves. The next page goes deeper on SQLite's operator set — arithmetic, string concatenation with ||, the IS family, three-valued logic — so the surprises stop being surprises.