SQLite § operators

Operators

SQL operators in SQLite are largely those of ISO SQL with a small number of additions (|| for string concatenation; IS / IS NOT for NULL-safe equality; GLOB, MATCH, REGEXP as named comparison operators; -> and ->> for JSON access from version 3.38). This page enumerates the operators in the order in which a programmer is likely to need them — arithmetic, comparison, logical, bitwise, string, the named predicates that take the place of operator forms in other languages — and notes the precedence and the NULL behaviour. NULL is the source of every operator surprise in SQL and is handled in a dedicated page (NULL semantics).

Arithmetic

Operator	Effect
`+`, `-`	Addition, subtraction (also unary)
`*`, `/`	Multiplication, division
`%`	Modulo (remainder)

Integer division returns an integer; one operand must be REAL (or 1.0 *) to obtain a real-valued quotient.

SELECT 7 / 2,    7 / 2.0,    7 % 3;
-- 3 | 3.5 | 1

The + operator is also unary (+x returns the value unchanged) and is sometimes used in ORDER BY to suppress index-based ordering when an explicit sort is desired. There is no exponentiation operator; the pow(x, y) mathematical function (added in 3.35) is the substitute. Division by zero returns NULL rather than raising an error.

Comparison

Operator	Effect
`=` or `==`	Equality
`<>` or `!=`	Inequality
`<`, `>`, `<=`, `>=`	Ordered comparison
`IS`, `IS NOT`	NULL-safe equality
`IS DISTINCT FROM`, `IS NOT DISTINCT FROM`	NULL-safe inequality / equality (3.39+)

The = and == operators are interchangeable; the doubled form is accepted for compatibility with C-derived languages. Comparison operators return one of 0, 1, or NULL. The = operator returns NULL if either operand is NULL — this is the standard SQL three-valued logic — which is why the conventional WHERE x = NULL is always false rather than selecting NULL rows. The remedy is IS NULL, or the SQLite-specific IS / IS NOT, which treats NULL as comparable and returns 1 if the operands are both NULL.

SELECT 1 = 1, 1 = 2, 1 = NULL, NULL = NULL;
-- 1 | 0 | NULL | NULL

SELECT 1 IS 1, 1 IS 2, 1 IS NULL, NULL IS NULL;
-- 1 | 0 | 0 | 1

Comparison between values of different storage classes follows the cross-class ordering described in Type affinity: NULL < numeric < text < BLOB. Programmers arriving from strictly typed engines should be aware that '2' < 10 returns 0 (text is greater than integer in SQLite’s cross-class ordering); explicit CAST is the remedy.

Logical

Operator	Effect
`AND`	Logical conjunction
`OR`	Logical disjunction
`NOT`	Logical negation

Logical operators implement three-valued logic: TRUE, FALSE, and NULL (the unknown value). The full truth table:

`x`	`y`	`x AND y`	`x OR y`	`NOT x`
1	1	1	1	0
1	0	0	1	0
1	NULL	NULL	1	0
0	0	0	0	1
0	NULL	0	NULL	1
NULL	NULL	NULL	NULL	NULL

The two short-circuit cases worth remembering: 0 AND NULL is 0 (a false conjunction is false regardless of the unknown), and 1 OR NULL is 1 (a true disjunction is true regardless of the unknown). All other combinations involving NULL produce NULL.

Range, set, and pattern predicates

The named predicates are operator-like forms that supplement the comparison operators.

BETWEEN / NOT BETWEEN

x BETWEEN a AND b is equivalent to x >= a AND x <= b. The bounds are inclusive. NOT BETWEEN is the negated form.

SELECT * FROM bouquet WHERE price BETWEEN 20 AND 30;
-- equivalent to: WHERE price >= 20 AND price <= 30

IN / NOT IN

x IN (a, b, c, …) returns true if x equals any of the listed values; the list may also be a subquery.

SELECT * FROM bouquet WHERE price IN (20, 26);
SELECT * FROM customer WHERE id IN (SELECT cust_id FROM bouquet_order);

The right-hand side may be a literal list of values or a one-column subquery. NOT IN returns true if x is unequal to every listed value; if any of the listed values is NULL and x does not match any non-NULL value, the result is NULL, which is one of the more-cited NULL surprises in SQL.

LIKE / NOT LIKE

x LIKE pattern returns true if the string x matches the pattern. Two metacharacters are recognised:

Metacharacter	Match
`%`	Zero or more characters
`_`	Exactly one character

SELECT * FROM bouquet WHERE bouquet_name LIKE 'A%';     -- starts with 'A'
SELECT * FROM bouquet WHERE bouquet_name LIKE '%love%'; -- contains 'love'
SELECT * FROM bouquet WHERE bouquet_name LIKE 'A__%';   -- starts with 'A', length >= 3

LIKE is case-insensitive for ASCII letters by default; the case-folding does not apply to non-ASCII letters. PRAGMA case_sensitive_like = ON makes LIKE case-sensitive throughout.

A literal % or _ is matched by introducing an ESCAPE clause:

SELECT * FROM file WHERE name LIKE '5\_%' ESCAPE '\';   -- starts with '5_'

GLOB

GLOB is identical to LIKE except that the metacharacters are the Unix glob characters (*, ?, [abc], [a-z], [^abc]) and the matching is case-sensitive. GLOB is the conventional choice when matching file paths or other case-sensitive identifiers.

SELECT * FROM file WHERE path GLOB 'src/*.c';

MATCH

MATCH is overloaded: with no virtual table involved, it is a synonym for GLOB; with an FTS5 or FTS4 virtual table, it invokes the full-text search machinery and accepts an FTS query string. MATCH is covered in detail under Full-text search.

REGEXP

x REGEXP pattern calls a user-defined function named regexp(pattern, x) to perform the match. SQLite does not bundle a regex implementation; bindings such as pysqlite and better-sqlite3 typically register one on connection, but a plain sqlite3 shell does not. The recommendation is to use LIKE or GLOB where a regex is not strictly required, or to load an extension that provides regexp (the regexp.c extension shipped in the SQLite source).

EXISTS / NOT EXISTS

EXISTS (subquery) returns 1 if the subquery returns any row, 0 otherwise. The subquery is evaluated as a test; its column values are discarded.

SELECT name FROM customer
WHERE EXISTS (
    SELECT 1 FROM bouquet_order WHERE bouquet_order.cust_id = customer.id
);

EXISTS is the conventional way to express correlated existence and frequently outperforms a JOIN when only the existence — not the matched row — is needed.

Bitwise

Operator	Effect
`&`	Bitwise AND
`	`
`~`	Bitwise NOT (unary)
`<<`, `>>`	Left shift, right shift

Bitwise operators apply to integer operands. There is no exclusive-or operator; XOR is expressed as (a | b) - (a & b) or the user-defined function form. Bitwise operators are useful for extracting bit fields from compact integer encodings and for the kind of low-level manipulation that the SQLite engine itself performs internally.

String

The string concatenation operator is || (the SQL standard form). There is no + for strings; 'a' + 'b' evaluates to 0 because the operands convert numerically and yield zero.

SELECT 'Ms ' || name FROM customer;
SELECT name || ' (' || age || ')' FROM customer;

|| propagates NULL: if either operand is NULL, the result is NULL. The coalesce(s, '') helper or IFNULL(s, '') is the conventional remedy.

The substantial library of string functions — length, substr, instr, replace, trim, upper, lower, printf, format — is documented under Strings and BLOBs and Built-in functions.

CASE

CASE expressions implement SQL’s conditional dispatch. Two forms are accepted: the simple form, which compares a single expression against a series of values, and the searched form, which evaluates a series of independent boolean conditions.

-- Simple form
SELECT CASE size
           WHEN 1 THEN 'small'
           WHEN 2 THEN 'medium'
           WHEN 3 THEN 'large'
           ELSE        'unknown'
       END
FROM product;

-- Searched form
SELECT CASE
           WHEN price < 10 THEN 'budget'
           WHEN price < 30 THEN 'standard'
           ELSE                 'premium'
       END
FROM bouquet;

If no WHEN clause matches and no ELSE clause is present, the expression evaluates to NULL. CASE is the conventional way to project derived values in a SELECT and to express conditional updates in an UPDATE.

JSON access

Two operators access values inside JSON-typed text columns, introduced in version 3.38:

Operator	Effect
`->`	Returns a JSON sub-value
`->>`	Returns a JSON sub-value as SQLite text or numeric

SELECT settings -> 'theme'      FROM user;   -- '"dark"'   (JSON-quoted text)
SELECT settings ->> 'theme'     FROM user;   --  dark      (unquoted)
SELECT settings -> 'roles' ->> 0 FROM user;

The path argument is a JSON-pointer-style expression ('$.theme') or, for top-level keys and array indices, the bare name. JSON support is documented in detail under JSON.

Operator precedence

The full precedence table, from tightest to loosest, is given in the SQLite reference at sqlite.org/lang_expr.html#operators. The frequently consulted summary is:

~, unary +, unary -
||, ->, ->>
*, /, %
+, -
&, |, <<, >>
<, <=, >, >=
=, ==, <>, !=, IS, IS NOT, IN, NOT IN, MATCH, LIKE, REGEXP, GLOB, BETWEEN, ISNULL, NOTNULL
NOT
AND
OR

The recommendation, here as in any language, is that parentheses are cheaper than the cognitive load of remembering a precedence table; explicit grouping is preferred whenever the reading might be ambiguous.

NULL and operator behaviour

A short summary, applicable to every operator on this page:

Arithmetic, bitwise, string, and JSON operators propagate NULL: any NULL operand yields NULL. The exception is the concatenation of an empty string, which still yields NULL because the operand is unknown.
Comparison operators (=, <>, <, <=, >, >=) return NULL if either operand is NULL.
Logical operators (AND, OR, NOT) follow three-valued logic, with the two short-circuit cases noted earlier.
The IS / IS NOT operators are NULL-safe and never return NULL.
IS DISTINCT FROM (3.39+) is the NULL-safe inequality and is symmetric with IS NOT DISTINCT FROM.
The IN operator may return NULL when the right-hand list contains NULL and the left-hand value does not match any non-NULL element.

The full treatment of NULL is on the NULL semantics page; the present summary is offered as a reminder when reading expressions on this page.