SQLite Expressions Explained with Examples

You write age >= 21 in a WHERE clause and the query runs. Put the same text in a DEFAULT clause and SQLite rejects the CREATE TABLE, with nothing in the message about why one place takes it and the other does not. Both are expressions, and SQLite accepts an expression almost everywhere it expects a value. The handful of places that narrow what an expression may contain each narrow it for a stated reason: no subquery here, constants only there.

Every example below runs against one table:

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
);

INSERT INTO students (id, name, age) VALUES
    (1, 'John', 20),
    (2, 'Sarah', 22),
    (3, 'Michael', 19),
    (4, 'Emily', 21),
    (5, 'David', 23);

For the mechanics of creating a table like this one, see our article on creating a table in SQLite.

What an expression is in SQLite

An expression is anything SQLite evaluates to a single value: a literal, a column reference, an operator applied to other expressions, or a function call[1]. 21 is an expression, age is an expression, and age >= 21 is an expression built from the first two.

The value that comes back has a storage class rather than a declared type. Boolean is not one of those storage classes. Where a clause needs a true or false answer, the WHERE clause of a SELECT and the WHEN clause of a CASE among them, SQLite casts the value to NUMERIC and reads zero as false and everything else as true. NULL stays NULL. That rule catches people out on text: 'english' is false and '1english' is true, because the cast reads as far as it can and stops.

The forms an expression takes

SQLite groups expressions into a handful of forms, and each is valid anywhere the grammar expects a value:

Operators bind in the precedence order the expression documentation lists, and one line of that order decides how most WHERE clauses read: AND binds tighter than OR. A condition written a AND b OR c is evaluated as (a AND b) OR c, so the parentheses you would have added are the ones to write down.

CASE is the form worth watching run. It evaluates its WHEN expressions from left to right and takes the THEN value belonging to the first one that is true, falling back to ELSE:

SELECT name, CASE WHEN age >= 21 THEN 'adult' ELSE 'minor' END AS age_group
FROM students;

With no ELSE and no matching WHEN, the result is NULL rather than an error, which is how a CASE quietly produces empty cells.

Where SQLite lets you use an expression

An expression is legal almost everywhere SQLite expects a value. Four contexts narrow what it may contain:

The DEFAULT clause takes a plain literal or, in parentheses, any expression SQLite considers constant[2], which rules out a subquery, a column reference and a bound parameter. That is the answer to the rejection at the top of this page: age >= 21 reads a column, and a DEFAULT has no row to read it from.

A generated column runs its expression against other columns of the same row, on every read, or once at write time when the column is declared STORED[3]. age_months above never has to be kept in step by hand. An index on an expression works the other way round: SQLite uses idx_double_age only for a query that writes age * 2 the way the index does, because the planner matches the expression text instead of doing algebra on it[4].

Expressions in the sqlite3 shell

Start the command-line shell that ships with SQLite, then open a file with .open sampleDB.db. SQLite creates it if it does not exist yet, and our article on creating a SQLite database covers the step in full.

sqlite3

Paste the CREATE TABLE and INSERT statements above, then try one expression of each kind. A comparison is a boolean expression, and in a WHERE clause it filters rows:

SELECT * FROM students WHERE age > 21;

Arithmetic computes a value per row, and the column header is the expression itself unless you give it an alias:

SELECT age * 2 FROM students;

A function call is an expression too, and the date functions take a starting point followed by modifiers applied left to right:

SELECT date('2026-03-01', '+7 days');

Expressions in the DbSchema SQL Editor

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

The same three queries run unchanged in DbSchema. Start DbSchema, choose Connect to Database, pick SQLite from the list of database types, and point the connection at the file you opened in the shell. Press Test Connection to check that the file answers, then Connect. If you have not settled on a SQLite client yet, see our comparison of SQLite database design tools.

Open the SQL Editor from the Editors menu or the toolbar.

Type a query and press Execute Query, which runs the statement at the cursor and shows the result as a table under the editor. Auto-complete is on Ctrl+Space and suggests the table and column names it read from the connected schema, which is how you find out that a column is called age and not student_age while writing the expression.

The SQL Editor sends the statement to the connected SQLite file, so a SELECT here reads the real rows and changes nothing in the DbSchema model file, which stores the diagram and the editors themselves. Every statement of the session is listed in the SQL History pane, and clicking an entry loads it back into the editor.

For a closer look at reverse-engineering and managing a SQLite schema visually, see how DbSchema designs and manages SQLite databases.

Once you can name the form of an expression, the restrictions stop being arbitrary: a DEFAULT refuses a column because it has no row, an index on an expression refuses a random function because the value has to stay put. Connecting to a SQLite file and running expressions in the SQL Editor is in the free DbSchema Community Edition. Download it at https://dbschema.com/download.html, point it at your own file, and start with the CASE query above.

Sources

1. SQLite Query Language: Expressions
2. SQLite Query Language: CREATE TABLE
3. SQLite Generated Columns
4. SQLite Indexes On Expressions