SQLite WHERE Clause: Filter Rows with Conditions (Tested Examples)

Without a WHERE clause, a SELECT returns every row in the table. WHERE keeps only the rows that match a condition, like one author’s books. In this article I’ll walk you through writing SQLite WHERE conditions, from a single = up to the NULL cases that trip up queries.

TLDR

• WHERE keeps only the rows whose condition is true: SELECT … FROM books WHERE author = 'Stephen King';
• Combine conditions with AND and OR. AND binds tighter than OR, so use parentheses when you mix them.
• Use IN (…) for a list of values and BETWEEN for an inclusive range.
• Use LIKE or GLOB for patterns.
• Test for missing values with IS NULL. = NULL never matches.
• The same WHERE works in UPDATE and DELETE.

What is the WHERE clause in SQLite?

WHERE is the part of a query that filters rows. SQLite checks the condition for each row and keeps the row only when the result is true. Rows where it’s false or NULL are dropped. It works the same way in UPDATE and DELETE as in SELECT.

SQLite WHERE syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Operator	Meaning	Example
= or ==	equal	author = 'Stephen King'
<> or !=	not equal	author <> 'Stephen King'
<, <=, >, >=	comparison	year >= 1987
BETWEEN a AND b	inclusive range	year BETWEEN 1977 AND 1987
IN (…)	matches any value in a list or subquery	author IN ('J.R.R. Tolkien', 'Michael Crichton')
LIKE	pattern with % and _, ignores ASCII case	title LIKE '%the%'
GLOB	pattern with * and ?, case-sensitive	title GLOB '*The*'
IS NULL, IS NOT NULL	missing value	year IS NULL
AND, OR, NOT	combine conditions	year > 1980 AND pages < 400

How to filter rows with WHERE

To show you how this works, I will use a small books table. The last book has no year or page count yet, so the NULL cases show up too:

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  year INTEGER,
  pages INTEGER
);
INSERT INTO books (title, author, year, pages) VALUES
  ('The Shining', 'Stephen King', 1977, 447),
  ('Jurassic Park', 'Michael Crichton', 1990, 399),
  ('The Hobbit', 'J.R.R. Tolkien', 1937, 310),
  ('It', 'Stephen King', 1986, 1138),
  ('Sphere', 'Michael Crichton', 1987, 385),
  ('Untitled draft', 'Stephen King', NULL, NULL);

sqlite> SELECT * FROM books;
id  title           author            year  pages
--  --------------  ----------------  ----  -----
1   The Shining     Stephen King      1977  447
2   Jurassic Park   Michael Crichton  1990  399
3   The Hobbit      J.R.R. Tolkien    1937  310
4   It              Stephen King      1986  1138
5   Sphere          Michael Crichton  1987  385
6   Untitled draft  Stephen King      NULL  NULL

Step 1: Match one value with =

sqlite> SELECT title, year FROM books WHERE author = 'Stephen King';
title           year
--------------  ----
The Shining     1977
It              1986
Untitled draft  NULL

<> (or !=) returns the opposite set:

sqlite> SELECT title, author FROM books WHERE author <> 'Stephen King';
title          author
-------------  ----------------
Jurassic Park  Michael Crichton
The Hobbit     J.R.R. Tolkien
Sphere         Michael Crichton

Step 2: Compare numbers or a range

Comparison operators work on numbers and on text, including dates stored as text:

sqlite> SELECT title, year FROM books WHERE year >= 1987;
title          year
-------------  ----
Jurassic Park  1990
Sphere         1987

BETWEEN includes both ends of the range. “x BETWEEN y AND z” is equivalent to “x >= y AND x <= z” (SQLite expressions), so 1977 and 1987 both match:

sqlite> SELECT title, year FROM books WHERE year BETWEEN 1977 AND 1987;
title        year
-----------  ----
The Shining  1977
It           1986
Sphere       1987

Step 3: Combine conditions with AND and OR

AND needs both conditions to be true:

sqlite> SELECT title, pages FROM books WHERE author = 'Michael Crichton' AND pages < 390;
title   pages
------  -----
Sphere  385

OR needs at least one:

sqlite> SELECT title, year FROM books WHERE year < 1950 OR pages > 1000;
title       year
----------  ----
The Hobbit  1937
It          1986

Examples

These examples cover the conditions you’ll need once the basics work.

Use parentheses when you mix AND and OR

AND has a higher precedence than OR (SQLite operators). Without parentheses, the first query below reads as “any Stephen King book, or a Michael Crichton book after 1988”. The parentheses in the second query give the intended meaning:

sqlite> SELECT title, year FROM books WHERE author = 'Stephen King' OR author = 'Michael Crichton' AND year > 1988;
title           year
--------------  ----
The Shining     1977
Jurassic Park   1990
It              1986
Untitled draft  NULL
sqlite> SELECT title, year FROM books WHERE (author = 'Stephen King' OR author = 'Michael Crichton') AND year > 1988;
title          year
-------------  ----
Jurassic Park  1990

Match a list of values with IN

IN replaces a chain of OR conditions:

sqlite> SELECT title, author FROM books WHERE author IN ('J.R.R. Tolkien', 'Michael Crichton');
title          author
-------------  ----------------
Jurassic Park  Michael Crichton
The Hobbit     J.R.R. Tolkien
Sphere         Michael Crichton

The list can also come from a subquery:

sqlite> CREATE TABLE favourites (author TEXT); INSERT INTO favourites VALUES ('J.R.R. Tolkien');
sqlite> SELECT title FROM books WHERE author IN (SELECT author FROM favourites);
title
----------
The Hobbit

Find text that contains a word with LIKE or GLOB

LIKE ignores case for ASCII letters, so '%the%' finds both titles that start with “The”:

sqlite> SELECT title FROM books WHERE title LIKE '%the%';
title
-----------
The Shining
The Hobbit

GLOB “is case sensitive, unlike LIKE” (SQLite expressions). The lowercase pattern finds nothing:

sqlite> SELECT count(*) AS lowercase_the FROM books WHERE title GLOB '*the*';
lowercase_the
-------------
0
sqlite> SELECT title FROM books WHERE title GLOB '*The*';
title
-----------
The Shining
The Hobbit

See SQLite LIKE and SQLite GLOB for the full pattern syntax.

Find missing values with IS NULL

A comparison with NULL is never true, so = NULL matches no rows. IS NULL finds the book with no year:

sqlite> SELECT count(*) AS eq_null FROM books WHERE year = NULL;
eq_null
-------
0
sqlite> SELECT title FROM books WHERE year IS NULL;
title
--------------
Untitled draft

Watch out for NOT IN and NULL

NOT IN skips rows whose value is NULL, so the untitled draft is missing from the first result. A NULL inside the list is worse: x NOT IN (…, NULL) can never be true, so it matches nothing:

sqlite> SELECT title, year FROM books WHERE year NOT IN (1977, 1990);
title       year
----------  ----
The Hobbit  1937
It          1986
Sphere      1987
sqlite> SELECT count(*) AS rows_matched FROM books WHERE 1937 NOT IN (1977, NULL);
rows_matched
------------
0

If the list comes from a subquery, add WHERE column IS NOT NULL inside it.

Match text without caring about case

= compares text exactly, so a lowercase name doesn’t match. COLLATE NOCASE ignores ASCII case for that comparison:

sqlite> SELECT count(*) AS exact FROM books WHERE author = 'stephen king';
exact
-----
0
sqlite> SELECT count(*) AS nocase FROM books WHERE author = 'stephen king' COLLATE NOCASE;
nocase
------
3

Use WHERE in UPDATE and DELETE

The same conditions pick the rows to change or remove. RETURNING shows which rows were hit:

sqlite> UPDATE books SET pages = 320 WHERE title = 'The Hobbit' RETURNING title, pages;
title       pages
----------  -----
The Hobbit  320
sqlite> DELETE FROM books WHERE year IS NULL RETURNING title;
title
--------------
Untitled draft
sqlite> SELECT count(*) AS books_left FROM books;
books_left
----------
5

Run an UPDATE or DELETE as a SELECT with the same WHERE first. Without a WHERE, it changes every row in the table.

Conclusion

Use = and the comparison operators for simple filters. Reach for IN and BETWEEN for lists and ranges. Use LIKE or GLOB for patterns. Put parentheses around OR groups, and use IS NULL for missing values. To sort or group the filtered rows, see the guides below.

Further reading

• SQLite SELECT statement
• SQLite ORDER BY
• SQLite HAVING clause
• SQLite LIKE
• SQLite GLOB
• SQLite docs: SQL expressions

Tested with SQLite 3.51.0 on 7 October 2026.