SQLite Operators: Arithmetic, Comparison, AND, OR, IN, LIKE and Precedence (Tested)

In SQLite, AND binds tighter than OR, so state = 'CA' OR state = 'NY' AND signed_up > '2022-01-01' doesn’t mean what it looks like. Precedence and NULL are where operators surprise people. In this article I’ll walk you through the SQLite operators grouped by job, with real output, including both traps.

How to use this page

Each section below covers one group of operators: a table first, then the output of a real query. Every query runs against this small customers table, which has a NULL age and a NULL state on purpose:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  state TEXT,
  age INTEGER,
  signed_up TEXT NOT NULL,
  flags INTEGER NOT NULL DEFAULT 0
);
INSERT INTO customers (name, state, age, signed_up, flags) VALUES
  ('Ada',   'CA', 30, '2021-06-01', 1),
  ('Linus', 'NY', 25, '2023-02-10', 3),
  ('Grace', 'NY', 20, '2021-03-15', 2),
  ('Ken',   'TX', NULL, '2024-01-20', 0),
  ('Barb',  NULL, 41, '2022-09-09', 5);

sqlite> SELECT * FROM customers;
id  name   state  age   signed_up   flags
--  -----  -----  ----  ----------  -----
1   Ada    CA     30    2021-06-01  1
2   Linus  NY     25    2023-02-10  3
3   Grace  NY     20    2021-03-15  2
4   Ken    TX     NULL  2024-01-20  0
5   Barb   NULL   41    2022-09-09  5

The full reference is SQLite’s SQL Language Expressions page.

Arithmetic operators

These do math on numbers.

Operator	Meaning	Example	Result
+	Add	7 + 2	9
-	Subtract	7 - 2	5
*	Multiply	7 * 2	14
/	Divide	7 / 2	3 (integer division)
%	Remainder (modulo)	7 % 2	1

sqlite> SELECT 7 + 2 AS plus, 7 - 2 AS minus, 7 * 2 AS times, 7 / 2 AS int_div, 7 / 2.0 AS real_div, 7 % 2 AS modulo, 7 / 0 AS div_zero;
plus  minus  times  int_div  real_div  modulo  div_zero
----  -----  -----  -------  --------  ------  --------
9     5      14     3        3.5       1       NULL

Notice the division results. Dividing two integers gives an integer, “truncated toward zero” (SQLite expressions), so use 7 / 2.0 for 3.5. Dividing by zero returns NULL, not an error, as the div_zero column shows.

Comparison operators

These compare two values and return 1 (true), 0 (false) or NULL.

Operator	Meaning
= or ==	Equal
!= or <>	Not equal
<, <=	Less than, less than or equal
>, >=	Greater than, greater than or equal

sqlite> SELECT name, age FROM customers WHERE age >= 25 AND age <> 30;
name   age
-----  ---
Linus  25
Barb   41

Both spellings of equal and not equal work the same:

sqlite> SELECT 1 = 1 AS single_eq, 1 == 1 AS double_eq, 1 != 2 AS bang_ne, 1 <> 2 AS ltgt_ne;
single_eq  double_eq  bang_ne  ltgt_ne
---------  ---------  -------  -------
1          1          1        1

Logical operators: AND, OR, NOT

These combine conditions. They work the same in any WHERE clause, including those of UPDATE and DELETE statements.

Operator	A row matches when
AND	Both conditions are true
OR	At least one condition is true
NOT	The condition is false

AND

AND returns rows where every condition holds. Linus and Grace both live in NY, but only Linus is over 21:

sqlite> SELECT name, state, age FROM customers WHERE state = 'NY' AND age > 21;
name   state  age
-----  -----  ---
Linus  NY     25

OR

OR returns rows where any condition holds:

sqlite> SELECT name, state FROM customers WHERE state = 'CA' OR state = 'TX';
name  state
----  -----
Ada   CA
Ken   TX

NOT

NOT flips a condition. Barb has no state (NULL), so she isn’t returned either way:

sqlite> SELECT name, state FROM customers WHERE NOT state = 'NY';
name  state
----  -----
Ada   CA
Ken   TX

Combining AND and OR

AND is evaluated before OR. Without parentheses, this query means “CA, or (NY and signed up after 2022)”, so Ada from 2021 sneaks in:

sqlite> SELECT name, state, signed_up FROM customers WHERE state = 'CA' OR state = 'NY' AND signed_up > '2022-01-01';
name   state  signed_up
-----  -----  ----------
Ada    CA     2021-06-01
Linus  NY     2023-02-10

Parentheses say what you meant:

sqlite> SELECT name, state, signed_up FROM customers WHERE (state = 'CA' OR state = 'NY') AND signed_up > '2022-01-01';
name   state  signed_up
-----  -----  ----------
Linus  NY     2023-02-10

NULL operators: IS, IS NOT, IS DISTINCT FROM

These are the only comparisons that treat NULL as a value.

= never matches NULL, because comparing anything with NULL gives NULL. Use IS:

sqlite> SELECT NULL = NULL AS eq, NULL IS NULL AS is_null, 5 IS NOT NULL AS is_not_null, NULL IS DISTINCT FROM 5 AS distinct_from;
eq    is_null  is_not_null  distinct_from
----  -------  -----------  -------------
NULL  1        1            1

So age = NULL finds nothing, and age IS NULL finds Ken:

sqlite> SELECT name FROM customers WHERE age = NULL;
sqlite> SELECT name FROM customers WHERE age IS NULL;
name
----
Ken

The logical operators follow three-valued logic. NULL means “unknown”, and the result stays NULL unless the other operand settles it:

sqlite> SELECT NULL AND 0 AS null_and_false, NULL AND 1 AS null_and_true, NULL OR 1 AS null_or_true, NULL OR 0 AS null_or_false, NOT NULL AS not_null;
null_and_false  null_and_true  null_or_true  null_or_false  not_null
--------------  -------------  ------------  -------------  --------
0               NULL           1             NULL           NULL

SQLite’s docs: “It is not possible for an IS or IS NOT expression to evaluate to NULL”, and IS DISTINCT FROM is another spelling of IS NOT (SQLite expressions).

Membership and range: IN, NOT IN, BETWEEN

These test a value against a list or a range.

Operator	Meaning	Equivalent
x IN (a, b)	x equals any value in the list	x = a OR x = b
x NOT IN (a, b)	x equals none of them	x <> a AND x <> b
x BETWEEN a AND b	Inclusive range	x >= a AND x <= b

sqlite> SELECT name, state FROM customers WHERE state IN ('NY', 'TX');
name   state
-----  -----
Linus  NY
Grace  NY
Ken    TX

BETWEEN includes both ends. Ages 20 and 30 are in:

sqlite> SELECT name, age FROM customers WHERE age BETWEEN 20 AND 30;
name   age
-----  ---
Ada    30
Linus  25
Grace  20

NOT IN has a trap. If the list or subquery contains a NULL, NOT IN can’t be true for any row. Here the age column has a NULL, so the second query returns nothing:

sqlite> SELECT name, state FROM customers WHERE state NOT IN ('NY', 'TX');
name  state
----  -----
Ada   CA
sqlite> SELECT name FROM customers WHERE id NOT IN (SELECT age FROM customers);

Use NOT EXISTS, or filter the NULLs out of the subquery with WHERE age IS NOT NULL.

Pattern operators: LIKE and GLOB

These match text against a pattern.

Operator	Wildcards	Case
LIKE	% any text, _ one character	Ignores case for ASCII letters
GLOB	* any text, ? one character	Case-sensitive

sqlite> SELECT name FROM customers WHERE name LIKE 'g%';
name
-----
Grace
sqlite> SELECT name FROM customers WHERE name GLOB 'g*';
sqlite> SELECT name FROM customers WHERE name GLOB 'G*';
name
-----
Grace

More in SQLite LIKE and SQLite GLOB.

String concatenation: ||

This one joins text.

|| joins strings. If any part is NULL, the whole result is NULL, as Barb’s row shows:

sqlite> SELECT name || ' (' || state || ')' AS label FROM customers;
label
----------
Ada (CA)
Linus (NY)
Grace (NY)
Ken (TX)
NULL

See SQLite string concatenation for concat() and other ways around the NULL.

Bitwise operators

These work on the bits of integers.

Operator	Meaning
&	Bitwise AND
|	Bitwise OR
<<, >>	Shift left, shift right
~	Bitwise NOT

sqlite> SELECT name, flags, flags & 1 AS bit0, flags | 4 AS with_bit2, flags << 1 AS shifted, ~flags AS inverted FROM customers WHERE id <= 3;
name   flags  bit0  with_bit2  shifted  inverted
-----  -----  ----  ---------  -------  --------
Ada    1      1     5          2        -2
Linus  3      1     7          6        -4
Grace  2      0     6          4        -3

Other operators

A couple more show up in everyday queries.

-> and ->> extract parts of JSON: -> returns JSON, and ->> returns an SQL value. See SQLite JSON:

sqlite> SELECT '{"plan": "pro", "seats": 5}' -> '$.seats' AS arrow, '{"plan": "pro"}' ->> '$.plan' AS double_arrow;
arrow  double_arrow
-----  ------------
5      pro

EXISTS returns 1 if a subquery finds any row. See SQLite IF EXISTS:

sqlite> SELECT EXISTS (SELECT 1 FROM customers WHERE state = 'TX') AS has_texas;
has_texas
---------
1

Operator precedence

From highest to lowest, per SQLite’s docs (SQLite expressions: operators). Operators on the same row share precedence:

Precedence	Operators
1 (highest)	~, unary +, unary -
2	COLLATE
3	||, ->, ->>
4	*, /, %
5	+, -
6	&, |, <<, >>
7	ESCAPE
8	<, >, <=, >=
9	=, ==, <>, !=, IS, IS NOT, IS DISTINCT FROM, IS NOT DISTINCT FROM, BETWEEN, IN, MATCH, LIKE, REGEXP, GLOB, ISNULL, NOTNULL, NOT NULL
10	NOT
11	AND
12 (lowest)	OR

When in doubt, add parentheses.

Common mistakes

• Mixing AND and OR without parentheses. AND wins, as the precedence trap above shows.
• = NULL instead of IS NULL. It matches nothing.
• NOT IN with a NULL in the list. It matches nothing either.
• Integer division. 7 / 2 is 3. Make one side a real number.
• Comparing numbers stored as text. As text, '10' < '9' is true. Between a number literal and a text literal, the number is always smaller, because SQLite sorts numbers before text. A column with numeric affinity converts the text first (SQLite data types: comparisons):

sqlite> SELECT 10 < 9 AS numbers, '10' < '9' AS text, 10 < '9' AS mixed;
numbers  text  mixed
-------  ----  -----
0        1     1

Store numbers in numeric columns. See SQLite data types.

Further reading

• SQLite WHERE clause
• SQLite SELECT statement
• SQLite UPDATE query
• SQLite DELETE query
• SQLite LIMIT
• SQLite AND operator
• SQLite OR operator
• SQLite GLOB
• SQLite data types
• SQLite syntax
• Introduction to SQLite
• SQLite docs: SQL Language Expressions

Tested with SQLite 3.51.0 on 7 October 2026.