Expressions

An expression is any combination of values, operators, column references, and function calls that Turso evaluates to produce a single value. Expressions appear in SELECT column lists, WHERE conditions, ORDER BY, GROUP BY, HAVING, CHECK constraints, DEFAULT values, and more.

Literals

Integer and real literals

SELECT 42;          -- integer literal
SELECT -17;         -- negative integer
SELECT 3.14;        -- real (floating-point) literal
SELECT 2.5e10;      -- scientific notation → 25000000000.0
SELECT 0xFF;        -- hexadecimal integer → 255

String literals

String literals use single quotes. To embed a single quote, double it:

SELECT 'hello world';      -- text literal
SELECT 'it''s a test';     -- embedded single quote
SELECT '';                 -- empty string

Double-quoted identifiers ("column_name") are not string literals. Use single quotes for string values.

Blob literals

Blob literals are hexadecimal strings prefixed with x or X:

SELECT x'CAFEBABE';         -- blob literal (4 bytes)
SELECT X'48454C4C4F';       -- blob literal: 'HELLO' as raw bytes

NULL literal

SELECT NULL;   -- the null value

Column references

Refer to a column by name, or qualify it with a table name to avoid ambiguity in joins:

SELECT name FROM users;               -- unqualified column
SELECT users.name FROM users;         -- table-qualified column
SELECT u.name FROM users AS u;        -- alias-qualified column

-- Qualification is required when two joined tables share a column name
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id;

Schema-qualified references (schema.table.column) are not supported in Turso.

Operators

Arithmetic operators

Operator	Description	Example	Result
`+`	Addition	`3 + 4`	`7`
`-`	Subtraction	`10 - 3`	`7`
`*`	Multiplication	`3 * 4`	`12`
`/`	Division	`10 / 3`	`3`
`-` (unary)	Negation	`-5`	`-5`

Division between two integers performs integer (truncating) division. Use a real operand or CAST for floating-point division:

SELECT 10 / 3;                    -- 3  (integer division)
SELECT 10 / 3.0;                  -- 3.3333333333333335
SELECT CAST(10 AS REAL) / 3;      -- 3.3333333333333335

Comparison operators

Operator	Description
`=` or `==`	Equal
`!=` or `<>`	Not equal
`<`	Less than
`>`	Greater than
`<=`	Less than or equal
`>=`	Greater than or equal

All comparison operators return 1 (true), 0 (false), or NULL if either operand is NULL.

SELECT 1 = 1;     -- 1
SELECT 1 != 2;    -- 1
SELECT NULL = 1;  -- NULL  (not 0!)

IS and IS NOT

IS and IS NOT work like = and != but treat NULL as an ordinary comparable value:

SELECT NULL IS NULL;      -- 1
SELECT NULL IS NOT NULL;  -- 0
SELECT 1 IS 1;            -- 1
SELECT 1 IS NOT 2;        -- 1

IS DISTINCT FROM and IS NOT DISTINCT FROM are SQL-standard aliases for the same behaviour:

SELECT NULL IS DISTINCT FROM NULL;  -- 0  (both NULL → not distinct)
SELECT NULL IS DISTINCT FROM 1;     -- 1  (NULL vs non-NULL → distinct)
SELECT 1 IS NOT DISTINCT FROM 1;    -- 1

Logical operators

Operator	Description
`AND`	True if both operands are true
`OR`	True if at least one operand is true
`NOT`	Inverts a boolean value

Logical operators follow three-valued logic: NULL AND 1 is NULL, NULL OR 1 is 1.

SELECT 1 AND 1;      -- 1
SELECT 1 AND 0;      -- 0
SELECT NULL AND 1;   -- NULL
SELECT NULL OR 1;    -- 1
SELECT NOT 0;        -- 1

String concatenation

SELECT 'hello' || ' ' || 'world';   -- 'hello world'
SELECT 'Count: ' || 42;             -- 'Count: 42'

Bitwise operators

Operator	Description	Example	Result
`&`	Bitwise AND	`5 & 3`	`1`
`\|`	Bitwise OR	`5 \| 3`	`7`
`~`	Bitwise NOT	`~5`	`-6`
`<<`	Left shift	`1 << 4`	`16`
`>>`	Right shift	`16 >> 2`	`4`

SELECT 5 & 3;    -- 1  (0101 & 0011 = 0001)
SELECT 5 | 3;    -- 7  (0101 | 0011 = 0111)
SELECT 1 << 3;   -- 8

Operator precedence

Operators are evaluated in this order (highest precedence first):

Precedence	Operators
1 (highest)	`~` (unary), `+` (unary), `-` (unary)
2	`\|\|` (concatenation)
3	`*`, `/`
4	`+`, `-`
5	`<<`, `>>`, `&`, `\|`
6	`<`, `<=`, `>`, `>=`
7	`=`, `==`, `!=`, `<>`, `IS`, `IS NOT`, `IS DISTINCT FROM`, `IN`, `LIKE`, `GLOB`, `REGEXP`, `BETWEEN`
8	`NOT`
9	`AND`
10 (lowest)	`OR`

Use parentheses to override precedence:

SELECT 2 + 3 * 4;    -- 14  (multiplication first)
SELECT (2 + 3) * 4;  -- 20  (parentheses override)

CAST expression

CAST converts a value to a specified type.

CAST(expression AS type-name)

SELECT CAST(3.7 AS INTEGER);     -- 3  (truncates toward zero)
SELECT CAST(42 AS TEXT);          -- '42'
SELECT CAST('123' AS INTEGER);    -- 123
SELECT CAST('abc' AS INTEGER);    -- 0  (non-numeric text)
SELECT CAST(NULL AS INTEGER);     -- NULL

The target type name follows the same affinity rules as column declarations — CAST(x AS VARCHAR(10)) applies TEXT affinity, and CAST(x AS NUMERIC) applies NUMERIC affinity.

CASE expression

The CASE expression provides conditional logic inside SQL.

Simple CASE

Compares a single expression against a list of values:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE default_result]
END

SELECT name,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_label
FROM users;

Searched CASE

Evaluates arbitrary boolean conditions:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE default_result]
END

SELECT name, score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

If no WHEN clause matches and there is no ELSE clause, the CASE expression returns NULL.

Pattern matching

LIKE

LIKE performs case-insensitive pattern matching for ASCII characters. % matches any sequence of zero or more characters; _ matches any single character.

expression [NOT] LIKE pattern [ESCAPE escape-char]

SELECT 'Hello World' LIKE 'hello%';          -- 1  (case-insensitive)
SELECT 'Hello World' LIKE 'H_llo%';          -- 1  (_ matches 'e')
SELECT 'Hello World' LIKE '%World';           -- 1
SELECT '10% off' LIKE '10\% off' ESCAPE '\'; -- 1  (literal %)

SELECT name FROM products WHERE name LIKE '%widget%';

GLOB

GLOB performs case-sensitive Unix-style pattern matching. * matches any sequence; ? matches a single character; [...] matches a character class.

expression [NOT] GLOB pattern

SELECT 'Hello' GLOB 'H*';       -- 1
SELECT 'Hello' GLOB 'h*';       -- 0  (case-sensitive)
SELECT 'Hello' GLOB 'H?llo';    -- 1
SELECT 'Hello' GLOB 'H[a-z]*';  -- 1  (character class)

REGEXP

REGEXP performs regular expression matching. The regexp extension is loaded by default in Turso.

expression [NOT] REGEXP pattern

SELECT 'Hello123' REGEXP '[A-Za-z]+[0-9]+';           -- 1
SELECT '[email protected]' REGEXP '^[^@]+@[^@]+\.[^@]+$'; -- 1
SELECT 'abc' NOT REGEXP '[0-9]+';                       -- 1

BETWEEN expression

BETWEEN tests whether a value falls within an inclusive range. It is equivalent to expression >= low AND expression <= high.

expression [NOT] BETWEEN low AND high

SELECT 5 BETWEEN 1 AND 10;       -- 1
SELECT 5 NOT BETWEEN 1 AND 3;    -- 1
SELECT 'b' BETWEEN 'a' AND 'c';  -- 1

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;

IN expression

IN tests whether a value matches any value in a list or subquery result.

expression [NOT] IN (value1, value2, ...)
expression [NOT] IN (select-statement)

SELECT 3 IN (1, 2, 3, 4, 5);             -- 1
SELECT 'red' NOT IN ('blue', 'green');    -- 1

-- IN with a subquery
SELECT name FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 100
);

EXISTS / NOT EXISTS

EXISTS returns 1 if the subquery produces at least one row, and 0 otherwise. It is commonly used as a correlated subquery in WHERE clauses.

[NOT] EXISTS (select-statement)

-- Does any user named Alice exist?
SELECT EXISTS (SELECT 1 FROM users WHERE name = 'Alice');  -- 1 or 0

-- Departments that have at least one employee
SELECT name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id
);

-- Departments with no employees
SELECT name FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id
);

Scalar subqueries

A subquery enclosed in parentheses that returns exactly one column and at most one row can be used as a scalar expression. If the subquery returns no rows, it evaluates to NULL.

SELECT name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

Turso supports scalar subqueries. Multi-column subquery comparisons such as (x, y) = (SELECT ...) are not supported.

NULL handling

Any arithmetic or comparison with NULL returns NULL. Use dedicated operators and functions to handle NULL explicitly.

IS NULL / IS NOT NULL

expression IS NULL
expression IS NOT NULL

SELECT NULL IS NULL;      -- 1
SELECT NULL = NULL;       -- NULL  (not 1!)
SELECT 42 IS NOT NULL;    -- 1
SELECT NULL IS NOT NULL;  -- 0

COALESCE

COALESCE(x, y, ...) returns the first non-NULL argument:

SELECT COALESCE(NULL, NULL, 'fallback');  -- 'fallback'
SELECT COALESCE(NULL, 42);               -- 42
SELECT COALESCE(1, 2, 3);               -- 1

-- Provide a default when a column may be NULL
SELECT name, COALESCE(phone, 'no phone') AS phone FROM users;

IFNULL

IFNULL(x, y) is equivalent to COALESCE(x, y) — returns x if it is not NULL, otherwise returns y:

SELECT IFNULL(NULL, 'default');  -- 'default'
SELECT IFNULL(42, 0);            -- 42

NULLIF

NULLIF(x, y) returns NULL if x = y, otherwise returns x. Useful for avoiding division by zero:

SELECT NULLIF(5, 5);   -- NULL
SELECT NULLIF(5, 3);   -- 5

-- Avoid division by zero
SELECT total / NULLIF(count, 0) AS average FROM stats;

Function calls

Functions are called with a comma-separated argument list. Most scalar functions, aggregate functions, and table-valued functions follow the same call syntax:

function-name(argument1, argument2, ...)

SELECT length('hello');          -- 5
SELECT upper('hello');           -- 'HELLO'
SELECT round(3.14159, 2);        -- 3.14
SELECT abs(-42);                 -- 42
SELECT coalesce(NULL, 0);        -- 0

-- Aggregate functions in SELECT
SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

-- Window function with OVER clause
SELECT name, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

See Scalar Functions, Aggregate Functions, and Window Functions for the complete function references.

Overview

Statements

Functions

Pragmas

Literals

Integer and real literals

String literals

Blob literals

NULL literal

Column references

Operators

Arithmetic operators

Comparison operators

IS and IS NOT

Logical operators

String concatenation

Bitwise operators

Operator precedence

CAST expression

CASE expression

Simple CASE

Searched CASE

Pattern matching

LIKE

GLOB

REGEXP

BETWEEN expression

IN expression

EXISTS / NOT EXISTS

Scalar subqueries

NULL handling

IS NULL / IS NOT NULL

COALESCE

IFNULL

NULLIF

Function calls

See also

Build docs developers (and LLMs) love

Overview

Statements

Functions

Pragmas

​Literals

​Integer and real literals

​String literals

​Blob literals

​NULL literal

​Column references

​Operators

​Arithmetic operators

​Comparison operators

​IS and IS NOT

​Logical operators

​String concatenation

​Bitwise operators

​Operator precedence

​CAST expression

​CASE expression

​Simple CASE

​Searched CASE

​Pattern matching

​LIKE

​GLOB

​REGEXP

​BETWEEN expression

​IN expression

​EXISTS / NOT EXISTS

​Scalar subqueries

​NULL handling

​IS NULL / IS NOT NULL

​COALESCE

​IFNULL

​NULLIF

​Function calls

​See also

Build docs developers (and LLMs) love

Literals

Integer and real literals

String literals

Blob literals

NULL literal

Column references

Operators

Arithmetic operators

Comparison operators

IS and IS NOT

Logical operators

String concatenation

Bitwise operators

Operator precedence

CAST expression

CASE expression

Simple CASE

Searched CASE

Pattern matching

LIKE

GLOB

REGEXP

BETWEEN expression

IN expression

EXISTS / NOT EXISTS

Scalar subqueries

NULL handling

IS NULL / IS NOT NULL

COALESCE

IFNULL

NULLIF

Function calls

See also