Operators in SQLite: A Complete Guide

Every meaningful SQL query I’ve ever written relies on operators — the symbols and keywords that let you compare values, do arithmetic, combine strings, and build logical conditions. They’re such a fundamental part of the language that it’s easy to take them for granted, but SQLite has its own particular set of operators, its own precedence rules, and a handful of quirks (especially around type handling and NULL) that are worth understanding thoroughly rather than picking up piecemeal. In this guide, I’ll go through every major category of operator SQLite supports, with practical examples for each.

What Operators Do

An operator is a symbol or keyword that performs an operation on one or two values (called operands), producing a result. SQLite operators fall into several broad categories: arithmetic, comparison, logical, bitwise, string, and a handful of special-purpose operators like IS, IN, BETWEEN, LIKE, and GLOB.

Arithmetic Operators

These perform mathematical calculations on numeric values.

SELECT 10 + 5;    -- addition: 15
SELECT 10 - 5;    -- subtraction: 5
SELECT 10 * 5;    -- multiplication: 50
SELECT 10 / 5;    -- division: 2
SELECT 10 % 3;    -- modulo (remainder): 1

A detail I mentioned in my expressions guide but that bears repeating here: SQLite performs integer division when both operands are integers, truncating any remainder rather than producing a decimal.

SELECT 7 / 2;         -- returns 3, not 3.5
SELECT 7.0 / 2;        -- returns 3.5
SELECT 7 / 2.0;        -- returns 3.5

If you need guaranteed floating-point division, make sure at least one operand is a real number, either as a literal (7.0) or via an explicit CAST(... AS REAL).

Unary arithmetic operators are also supported:

SELECT -5;    -- unary minus, produces -5
SELECT +5;    -- unary plus, produces 5 (rarely used, since it's a no-op)

Comparison Operators

These compare two values and produce a result of 1 (true), 0 (false), or NULL (unknown, if either operand is NULL).

WHERE salary = 60000;    -- equal to
WHERE salary != 60000;   -- not equal to
WHERE salary <> 60000;   -- also not equal to (identical to !=)
WHERE salary > 60000;    -- greater than
WHERE salary < 60000;    -- less than
WHERE salary >= 60000;   -- greater than or equal to
WHERE salary <= 60000;   -- less than or equal to

!= and <> are fully interchangeable in SQLite — there’s no functional difference, it’s purely a matter of style. I tend to use != out of habit, but you’ll see <> in plenty of SQL codebases, especially ones influenced by older SQL standards documentation.

Logical Operators

AND, OR, and NOT combine boolean expressions.

WHERE department = 'Sales' AND salary > 60000;
WHERE department = 'Sales' OR department = 'Marketing';
WHERE NOT is_deleted;

I’ve written an entire dedicated guide on AND/OR precedence and their interaction with NULL, since it’s genuinely one of the most bug-prone areas in everyday SQL — the short version is that AND binds more tightly than OR, and you should always use explicit parentheses when combining the two.

The IS and IS NOT Operators

IS and IS NOT are used specifically for NULL-safe comparisons, and they behave differently from = and != in an important way.

WHERE manager_id IS NULL;
WHERE manager_id IS NOT NULL;

Beyond NULL checks, IS and IS NOT can also be used as NULL-safe equality operators for regular values, unlike = which always returns NULL (not true or false) when either operand is NULL:

SELECT NULL = NULL;       -- returns NULL, not true
SELECT NULL IS NULL;      -- returns 1 (true)
SELECT 5 IS 5;             -- returns 1 (true), behaves like = for non-null values
SELECT 5 IS NOT 6;         -- returns 1 (true)

This distinction matters more than people expect. If you’re ever comparing two columns where either might independently be NULL, and you want the comparison to correctly treat “both NULL” as equal, use IS instead of =:

SELECT * FROM table_a a
JOIN table_b b ON a.value IS b.value;  -- treats NULL = NULL as a match

With a plain = in that JOIN condition, rows where both a.value and b.value are NULL would never match, because NULL = NULL evaluates to NULL, not true.

The BETWEEN Operator

BETWEEN checks whether a value falls within an inclusive range.

WHERE price BETWEEN 10 AND 50;
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

It can be negated with NOT BETWEEN:

WHERE price NOT BETWEEN 10 AND 50;

Both endpoints are inclusive, so price BETWEEN 10 AND 50 matches a price of exactly 10 or exactly 50, in addition to everything in between.

The IN Operator

IN checks membership in a list of literal values or the results of a subquery.

WHERE category IN ('Electronics', 'Appliances', 'Furniture');

WHERE department_id IN (SELECT department_id FROM departments WHERE region = 'West');

It’s a cleaner alternative to a long chain of OR conditions comparing the same column against multiple values, and it can be negated with NOT IN. As I noted in my WHERE clause guide, be careful with NOT IN against a subquery or list that might contain NULL — a single NULL in that list causes the entire condition to evaluate to NULL for every row, silently returning zero results.

The LIKE and GLOB Operators

LIKE and GLOB are SQLite’s built-in pattern-matching operators, each with distinct behavior — I have dedicated guides on both, but here’s the essential comparison:

WHERE name LIKE 'J%';    -- case-insensitive, % and _ wildcards
WHERE name GLOB 'J*';    -- case-sensitive, * and ? wildcards, supports [A-Z] character classes

LIKE is generally better for user-facing, case-insensitive search. GLOB is better when case sensitivity or structured character-class matching matters — file paths, product codes, and similarly formatted identifiers.

String Concatenation Operator (||)

SQLite uses the double-pipe operator for joining text values together — this is the standard SQL approach, distinct from functions like CONCAT() that other database systems favor.

SELECT first_name || ' ' || last_name AS full_name FROM users;

If any operand in a || expression is NULL, the entire result becomes NULL — there’s no automatic NULL-skipping the way there is with, say, COALESCE. If you need to concatenate values that might be NULL while treating NULL as an empty string, wrap the potentially-NULL operand in COALESCE first:

SELECT first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name AS full_name FROM users;

Bitwise Operators

SQLite supports a standard set of bitwise operators for working directly with the binary representation of integer values.

SELECT 5 & 3;     -- bitwise AND: 1
SELECT 5 | 3;     -- bitwise OR: 7
SELECT ~5;        -- bitwise NOT (complement): -6
SELECT 5 << 1;    -- left shift: 10
SELECT 5 >> 1;    -- right shift: 2

These come up less often in typical application-level SQL, but they’re genuinely useful for working with bitmask-style flag columns, where individual bits represent different boolean states packed into a single integer column.

-- Check if the 3rd bit flag is set
SELECT * FROM permissions WHERE (flags & 4) = 4;

Operator Precedence

Understanding the order SQLite evaluates operators in — absent explicit parentheses — is essential for writing correct expressions. From highest to lowest precedence, roughly:

1. ~ + - (unary operators, bitwise NOT, unary plus/minus)
2. || (string concatenation)
3. * / % (multiplication, division, modulo)
4. + - (addition, subtraction)
5. << >> & | (bitwise shifts and bitwise AND/OR)
6. < <= > >= (comparison operators)
7. = == != <> IS IS NOT IN LIKE GLOB MATCH REGEXP BETWEEN (equality and pattern operators)
8. AND
9. OR

The practical implication of this ordering shows up constantly:

SELECT 2 + 3 * 4;                 -- 14, because * happens before +
SELECT 'a' || 'b' = 'ab';          -- true, because || happens before =
SELECT department = 'Sales' OR department = 'Marketing' AND salary > 90000;
-- AND binds tighter than OR here, which can produce unintended grouping

Whenever an expression mixes operators from different precedence tiers and the exact evaluation order genuinely matters to the outcome, I add parentheses explicitly. It costs nothing and removes all ambiguity for the next person reading the query.

Set Operators: UNION, INTERSECT, EXCEPT

Beyond the value-level operators covered above, SQLite also supports operators that work at the level of entire query results, combining the output of two or more SELECT statements.

-- UNION: combines results, removing duplicates
SELECT name FROM employees WHERE department = 'Sales'
UNION
SELECT name FROM employees WHERE department = 'Marketing';

-- UNION ALL: combines results, keeping duplicates (faster, since no dedup step)
SELECT name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT name FROM employees WHERE department = 'Marketing';

-- INTERSECT: only rows appearing in both result sets
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

-- EXCEPT: rows in the first result set that don't appear in the second
SELECT customer_id FROM all_customers
EXCEPT
SELECT customer_id FROM customers_with_orders;

All three require that the combined SELECT statements have the same number of columns, with compatible types in corresponding positions.

Type Affinity and Operator Behavior

Because SQLite uses dynamic typing with “type affinity” rather than strict column typing, operators can sometimes behave in ways that surprise people coming from more strictly-typed databases. For instance, comparing a numeric-looking text value to an actual number can produce results based on SQLite’s type conversion rules, which aren’t always intuitive.

SELECT '5' = 5;          -- returns 0 (false) — text '5' is NOT equal to integer 5 in SQLite's comparison rules
SELECT CAST('5' AS INTEGER) = 5;  -- returns 1 (true), after explicit conversion

Whenever there’s any doubt about whether values on either side of a comparison operator have the type you expect, I explicitly CAST them rather than relying on SQLite’s implicit type coercion — it’s a small habit that prevents a surprising number of subtle bugs.

Common Mistakes to Avoid

1. Assuming = handles NULL comparisons correctly. Use IS / IS NOT for NULL-safe comparisons instead.
2. Forgetting integer division truncates results. Cast to REAL explicitly when fractional results matter.
3. Mixing AND/OR without parentheses, unintentionally changing the logical grouping due to precedence.
4. Concatenating potentially-NULL values with || and being surprised the entire result becomes NULL. Use COALESCE to guard against this.
5. Comparing text and numeric types without explicit casting, leading to unexpected true/false results due to SQLite’s type affinity rules.

Best Practices

• Use IS / IS NOT instead of = / != whenever NULL might legitimately appear on either side of a comparison.
• Explicitly cast operands with CAST() whenever type ambiguity could affect the result of a comparison or arithmetic operation.
• Add parentheses generously in any expression mixing multiple operator precedence tiers.
• Wrap potentially-NULL operands in COALESCE before using them in || concatenation.
• Choose UNION ALL over UNION when you know there won’t be duplicates (or don’t care about them), since it skips the deduplication step and performs better.
• Reserve bitwise operators for genuine bitmask/flag scenarios — they’re powerful but not something you’ll need in most everyday application queries.

Operators are the smallest, most fundamental building blocks of every SQLite query you’ll ever write, and yet their edge cases — NULL handling, type coercion, precedence — are responsible for a disproportionate share of the subtle bugs that show up in real applications. Take the time to understand them properly, and a huge category of “why is this query behaving weirdly” problems simply disappears.

Frequently Asked Questions

Is there a dedicated boolean type in SQLite? Not really — SQLite doesn’t have a distinct BOOLEAN storage class. The keywords TRUE and FALSE are accepted as literals (since SQLite 3.23.0) but are internally stored and treated as the integers 1 and 0. This means comparison and logical operators always ultimately resolve to integer or NULL results, even though conceptually you can think of them as producing boolean values.

What does the == operator do — is it different from =? No difference at all. SQLite accepts both = and == for equality comparisons, purely as a stylistic convenience for developers coming from C-like languages where == is the equality operator and a single = is assignment. I stick with the single = in SQL since assignment isn’t a concern in this context, but both work identically.

Can I define my own custom operators in SQLite? Not operators in the strict syntactic sense (you can’t invent new operator symbols), but you can register custom scalar or aggregate functions through SQLite’s C API (or through bindings in your application language, like Python’s sqlite3.create_function()), which effectively gives you custom, reusable computation logic that behaves like a function-based “operator” within your expressions.

Does operator behavior change based on column type affinity? Yes, meaningfully so. SQLite’s type affinity system means a column declared as INTEGER will attempt to coerce inserted text that looks numeric into an actual integer, which then affects how comparison operators behave against that column later. This is part of why I’m cautious about implicit type coercion and prefer explicit CAST when there’s any doubt about what type a comparison is actually operating on.

The REGEXP Operator: An Important Caveat

You’ll sometimes see SQL referencing a REGEXP operator, and it’s worth clarifying its status in SQLite specifically, since it behaves differently than every other operator covered in this guide. SQLite’s parser recognizes the REGEXP keyword syntactically, but it does NOT include a built-in regular expression engine by default. Using REGEXP without registering a custom implementation function raises a runtime error.

SELECT * FROM products WHERE name REGEXP '^Pro.*';
-- Fails with "no such function: regexp" unless a REGEXP function has been registered

To actually use REGEXP, your application needs to register a custom function (often via the C API, or through libraries like Python’s sqlite3 module combined with the re module) that SQLite calls internally whenever the REGEXP operator is used. This is genuinely one of the most common points of confusion for developers moving to SQLite from a database that has full built-in regex support, like PostgreSQL.

Operators and Collation

Comparison operators on text values (=, <, >, etc.) are affected by the collating sequence associated with the column or explicitly specified in the query. By default, SQLite uses BINARY collation, meaning comparisons are based on raw byte values, which produces case-sensitive, locale-unaware ordering and matching.

SELECT 'Apple' = 'apple';                        -- 0 (false), binary collation is case-sensitive
SELECT 'Apple' = 'apple' COLLATE NOCASE;          -- 1 (true), NOCASE ignores ASCII case
SELECT 'Apple' < 'banana';                        -- 1 (true), 'A' (65) sorts before 'b' (98) in binary/ASCII order

This collation-sensitivity applies to every comparison operator, not just = — it’s worth remembering any time you’re comparing or sorting text and the results don’t match your intuition about “normal” alphabetical or case-insensitive ordering.

A Full Reference Table of Common SQLite Operators

For a quick-reference summary, here’s essentially every operator covered across arithmetic, comparison, logical, and special-purpose categories in one place:

Operator	Category	Description
+ - * / %	Arithmetic	Addition, subtraction, multiplication, division, modulo
= == != <>	Comparison	Equality and inequality
< <= > >=	Comparison	Relational comparisons
AND OR NOT	Logical	Boolean combination and negation
IS IS NOT	Comparison	NULL-safe equality/inequality
IN NOT IN	Membership	Set membership testing
BETWEEN NOT BETWEEN	Range	Inclusive range testing
LIKE NOT LIKE	Pattern matching	Case-insensitive wildcard matching
GLOB NOT GLOB	Pattern matching	Case-sensitive shell-style matching
`		`
& `	~<<>>`	Bitwise
UNION UNION ALL INTERSECT EXCEPT	Set	Combine results of multiple SELECTs

Final Thoughts on Choosing the Right Operator

After years of working with SQLite, the operator-selection decisions that matter most in practice usually come down to a small handful of recurring judgment calls: whether to use = or IS (depends on whether NULL is a realistic possibility), whether to use LIKE or GLOB (depends on case sensitivity needs), whether to use IN or a chain of OR (a readability call once you’re past two or three values), and whether implicit type coercion is safe to rely on or whether an explicit CAST is warranted (when in doubt, cast explicitly). Getting comfortable with these decisions, rather than reaching for whatever operator happens to “just work” in a quick test, is really what separates a fragile query from a genuinely reliable one.