Expressions & operators

Operator precedence

Expressions evaluate with standard precedence, from tightest to loosest:

Parentheses and unary + / - / NOT
*, /, %
+, -, ||
comparison =, <>/!=, <, <=, >, >=
NOT
AND
OR

IS NULL, IN, BETWEEN, and LIKE are predicates parsed at the comparison level.

Arithmetic

Operator	Meaning
`+ - *`	numeric
`/`	division — integer when both operands are integers (truncates toward zero)
`%`	modulo
`

Arithmetic on a NULL operand yields NULL.
Division or modulo by zero returns NULL, not an error.
Integer operands produce integer results; mixing a real promotes to real. Strings that parse as numbers are coerced.
|| renders both sides as text and never propagates NULL: NULL || 'x' → 'x'.

Comparison

=, <> (or !=), <, <=, >, >= follow three-valued logic: if either operand is NULL, the result is NULL (which behaves as false in a WHERE filter).

Comparison first tries to compare numerically (if both sides parse as numbers) and falls back to lexicographic string comparison otherwise.

Logical operators

AND and OR implement SQL three-valued logic:

	TRUE	FALSE	NULL
`TRUE AND`	TRUE	FALSE	NULL
`FALSE AND`	FALSE	FALSE	FALSE
`NULL AND`	NULL	FALSE	NULL
`TRUE OR`	TRUE	TRUE	TRUE
`FALSE OR`	TRUE	FALSE	NULL
`NULL OR`	TRUE	NULL	NULL

NOT NULL is NULL.

CASE

Both forms are supported:

-- simple CASE
SELECT CASE status WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 0 END FROM t;

-- searched CASE
SELECT CASE WHEN x > 0 THEN 'pos' WHEN x < 0 THEN 'neg' ELSE 'zero' END FROM t;

A simple CASE with a NULL operand matches nothing.
A searched CASE treats a NULL condition as false.
No ELSE and no match returns NULL.

CAST

CAST(expr AS TYPE)

TYPE may be any recognized type name. CAST(NULL AS anything) is NULL. Conversions:

to INTEGER-family → truncates toward zero (int64(value)).
to REAL/FLOAT/DOUBLE/NUMERIC/DECIMAL → float.
to TEXT/VARCHAR/CHAR → string rendering.
Any other target returns the value unchanged.

Predicates

IS NULL

x IS NULL
x IS NOT NULL

IN

x IN (1, 2, 3)
x IN (SELECT ...)      -- exactly one column
x NOT IN (...)

Full three-valued logic: NULL IN (...) is NULL; an empty list is FALSE for IN and TRUE for NOT IN; a list containing NULL with no match yields NULL.

BETWEEN

x BETWEEN low AND high
x NOT BETWEEN low AND high

Equivalent to x >= low AND x <= high (respectively x < low OR x > high) with three-valued NULL handling.

LIKE

x LIKE 'pattern'
x NOT LIKE 'pattern'
x LIKE 'pattern' ESCAPE 'char'

% matches any sequence, _ matches a single character.
Matching is case-insensitive (both sides are lowercased).
The ESCAPE clause is parsed but ignored — the escape character is treated literally.

EXISTS

EXISTS (SELECT ...)

Returns TRUE if the subquery returns at least one row, including for correlated subqueries.

Subqueries

Scalar subqueries return the first column of the first row, or NULL when empty. Unlike strict SQL, a multi-row scalar subquery returns the first value rather than erroring.
Correlated subqueries reference outer columns and are re-evaluated per outer row.
Non-correlated IN (SELECT ...) results are cached for the duration of the enclosing query.
A small, specialized optimization decorrelates scalar aggregate subqueries of the form (SELECT COUNT(*)/SUM/AVG/MIN/MAX(col) FROM inner WHERE inner.key = outer.key).

Column references

Unqualified references resolve against the visible tables; ambiguous references across joined tables are an analysis error.
Qualified references use table.column or alias.column.
rowid, oid, and _rowid_ resolve to the implicit rowid.

Constant folding

A WHERE clause that references no columns (e.g. WHERE 1 = 1) is evaluated once; a constant-false WHERE short-circuits to an empty result (or a single aggregated row for aggregate queries). This is an optimization, not a guarantee, and doesn’t imply a general planner.