SQLite Expressions: Arithmetic, Comparisons, CASE and NULL (Tested)

7 / 2 returns 3 in SQLite, and a NULL anywhere in an arithmetic expression makes the whole result NULL. Expressions follow a handful of rules like these, and in this article I’ll walk you through each kind of SQLite expression with real output, including the NULL and integer-division cases.

TLDR

• An expression is anything that produces a value, such as price * qty or upper(item).
• Dividing two integers gives an integer: 7 / 2 is 3. Write 7 / 2.0 for 3.5. Dividing by zero gives NULL.
• Comparisons return 1 for true and 0 for false. Anything compared with NULL gives NULL, so test with IS NULL.
• Use coalesce(x, default) to replace NULL before doing arithmetic with it.

What is an SQLite expression?

An expression is a piece of SQL that SQLite evaluates to a single value. You use expressions in the SELECT list and in clauses like WHERE. They also work in indexes and generated columns. The full grammar is on the SQLite expressions page.

Kind	Examples
Literal	42, 3.5, 'text', NULL
Column	price, orders.qty
Operator	price * qty, a = b, x AND y, 'a' || 'b'
Function	round(x, 2), upper(item), date('now')
CASE	CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END
CAST	CAST('42' AS INTEGER)
Subquery	(SELECT avg(price) FROM orders), EXISTS (...)

SQLite expression syntax

Expressions combine with operators. When there are no parentheses, SQLite applies precedence, from highest to lowest in this short list:

Precedence	Operators
1	|| (concatenation)
2	*, /, %
3	+, -
4	<, <=, >, >=
5	=, ==, <>, !=, IS, IN, LIKE, BETWEEN
6	NOT
7	AND
8	OR

The full table, with every operator, is in the docs (Operators). When in doubt, add parentheses.

How to use expressions in a query

To show you each kind, I will use a small orders table.

Step 1: Create the table

sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, price REAL NOT NULL, qty INTEGER NOT NULL, discount REAL, ordered_at TEXT NOT NULL);
sqlite> INSERT INTO orders (item, price, qty, discount, ordered_at) VALUES ('Pen', 1.5, 10, NULL, '2026-10-01'), ('Mug', 8.0, 2, 0.1, '2026-10-03'), ('Lamp', 24.0, 1, 0.25, '2026-09-28'), ('Desk', 180.0, 1, NULL, '2026-10-05');

Step 2: Calculate with columns

An expression in the SELECT list becomes a new column. AS gives it a name:

sqlite> SELECT item, price * qty AS total, round(price * qty * (1 - coalesce(discount, 0)), 2) AS after_discount FROM orders;
item  total  after_discount
----  -----  --------------
Pen   15.0   15.0
Mug   16.0   14.4
Lamp  24.0   18.0
Desk  180.0  180.0

coalesce(discount, 0) turns a missing discount into 0. Without it, the NULL would make the whole result NULL.

Step 3: Filter with an expression

WHERE keeps the rows for which the expression is true:

sqlite> SELECT item FROM orders WHERE (qty > 1 OR price > 100) AND NOT item = 'Mug';
item
----
Pen
Desk

Examples

The examples use the same orders table, or literal values.

Arithmetic operators

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

7 / 2 is 3 because both sides are integers. % gives the remainder, and division by zero returns NULL instead of an error.

Comparison operators

Comparisons return 1 or 0. = and == mean the same thing, and so do <> and !=. Text comparison is case-sensitive by default:

sqlite> SELECT 5 > 3 AS gt, 5 = 5.0 AS eq, 'abc' < 'abd' AS text_lt, 'a' = 'A' AS case_eq, 5 <> 3 AS ne, 5 == 5 AS double_eq;
gt  eq  text_lt  case_eq  ne  double_eq
--  --  -------  -------  --  ---------
1   1   1        0        1   1

NULL in expressions

NULL means “unknown”, so expressions that touch it return NULL (NULL handling):

sqlite> SELECT NULL = NULL AS eq_null, NULL IS NULL AS is_null, 1 + NULL AS plus_null, NULL AND 0 AS null_and_false, NULL OR 1 AS null_or_true, coalesce(NULL, 'fallback') AS coalesce_, nullif(5, 5) AS nullif_;
eq_null  is_null  plus_null  null_and_false  null_or_true  coalesce_  nullif_
-------  -------  ---------  --------------  ------------  ---------  -------
NULL     1        NULL       0               1             fallback   NULL

NULL = NULL isn’t true, it’s NULL. Use IS NULL or IS NOT NULL to test for it. NULL AND 0 is 0 and NULL OR 1 is 1, because the answer doesn’t depend on the unknown value.

String expressions

|| joins strings and converts numbers to text along the way:

sqlite> SELECT item || ' x' || qty AS line, upper(item) AS up, length(item) AS len, substr(item, 1, 2) AS first_two FROM orders LIMIT 2;
line     up   len  first_two
-------  ---  ---  ---------
Pen x10  PEN  3    Pe
Mug x2   MUG  3    Mu

CASE and iif()

CASE picks a value by condition, checking the WHEN branches in order. iif(condition, a, b) is the short form for two choices:

sqlite> SELECT item, price, CASE WHEN price >= 100 THEN 'large' WHEN price >= 10 THEN 'medium' ELSE 'small' END AS size, iif(discount IS NULL, 'full price', 'discounted') AS pricing FROM orders;
item  price  size    pricing
----  -----  ------  ----------
Pen   1.5    small   full price
Mug   8.0    small   discounted
Lamp  24.0   medium  discounted
Desk  180.0  large   full price

CAST and type conversion

CAST converts a value to another type. SQLite also converts text that looks like a number when you do arithmetic with it:

sqlite> SELECT CAST('42' AS INTEGER) + 1 AS from_text, CAST(9.99 AS INTEGER) AS truncated, typeof(CAST(3 AS REAL)) AS real_type, '10' + 5 AS implicit;
from_text  truncated  real_type  implicit
---------  ---------  ---------  --------
43         9          real       15

Date expressions

Date functions take text dates and modifiers like '+30 days':

sqlite> SELECT item, ordered_at, date(ordered_at, '+30 days') AS due, julianday('2026-10-07') - julianday(ordered_at) AS days_ago FROM orders WHERE ordered_at >= date('2026-10-07', '-7 days');
item  ordered_at  due         days_ago
----  ----------  ----------  --------
Pen   2026-10-01  2026-10-31  6.0
Mug   2026-10-03  2026-11-02  4.0
Desk  2026-10-05  2026-11-04  2.0

See SQLite date and time for the full set.

Math functions

Functions like ceil() and sqrt() are only available when SQLite is compiled with SQLITE_ENABLE_MATH_FUNCTIONS (math functions). The sqlite3 shell used here has them. abs() and round() are core functions that are always there:

sqlite> SELECT ceil(15.2) AS ceil_, floor(15.7) AS floor_, round(15.7) AS round_, abs(-3) AS abs_, power(2, 10) AS pow, sqrt(16) AS sqrt_;
ceil_  floor_  round_  abs_  pow     sqrt_
-----  ------  ------  ----  ------  -----
16.0   15.0    16.0    3     1024.0  4.0

Subquery expressions

A subquery in parentheses returns one value. EXISTS returns 1 if the subquery finds any row:

sqlite> SELECT item, price, price > (SELECT avg(price) FROM orders) AS above_avg FROM orders;
item  price  above_avg
----  -----  ---------
Pen   1.5    0
Mug   8.0    0
Lamp  24.0   0
Desk  180.0  1
sqlite> SELECT EXISTS (SELECT 1 FROM orders WHERE price > 100) AS has_big_order;
has_big_order
-------------
1

IN, BETWEEN and LIKE

sqlite> SELECT item FROM orders WHERE item IN ('Pen', 'Desk') OR price BETWEEN 5 AND 10 OR item LIKE 'L%';
item
----
Pen
Mug
Lamp
Desk

Precedence

* binds tighter than +, and AND tighter than OR:

sqlite> SELECT 2 + 3 * 4 AS no_parens, (2 + 3) * 4 AS parens, 1 OR 0 AND 0 AS or_and, (1 OR 0) AND 0 AS grouped;
no_parens  parens  or_and  grouped
---------  ------  ------  -------
14         20      1       0

Group and sort by an expression

sqlite> SELECT CASE WHEN price >= 10 THEN 'big' ELSE 'small' END AS bucket, count(*) AS n, sum(price * qty) AS revenue FROM orders GROUP BY bucket ORDER BY revenue DESC;
bucket  n  revenue
------  -  -------
big     2  204.0
small   2  31.0

Index an expression

An index on lower(item) lets SQLite use it for a case-insensitive lookup written the same way:

sqlite> CREATE INDEX idx_orders_lower_item ON orders(lower(item));
sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE lower(item) = 'pen';
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_lower_item (<expr>=?)

Store an expression as a generated column

sqlite> ALTER TABLE orders ADD COLUMN total REAL GENERATED ALWAYS AS (price * qty) VIRTUAL;
sqlite> SELECT item, total FROM orders ORDER BY total DESC LIMIT 2;
item  total
----  -----
Desk  180
Lamp  24

Edge cases

• Integer division. Any expression with two integer operands divides as integers. Multiply by 1.0 or write a decimal literal to get a real result.
• Division by zero. SQLite returns NULL, not an error, so a NULL in your results can come from a zero divisor.
• NULL comparisons in WHERE. WHERE discount = NULL matches nothing. Use WHERE discount IS NULL.
• Missing math functions. On a build without SQLITE_ENABLE_MATH_FUNCTIONS, ceil() fails with “no such function”.

Conclusion

Everything an SQLite query computes, from a column total to a sort key, is an expression. Watch for integer division and NULLs. Reach for coalesce() and IS NULL, and add parentheses when operators mix. For the operators themselves, see SQLite operators.

Further reading

• SQLite operators
• SQLite date and time
• SQLite data types
• SQLite CREATE INDEX
• sqldocs home
• SQLite docs: SQL expressions

Tested with SQLite 3.51.0 on 7 October 2026.