When people first learn SQL, they usually think about it in terms of clauses — SELECT, WHERE, ORDER BY, and so on. But underneath almost every one of those clauses is something even more fundamental: expressions. An expression is any combination of values, operators, functions, and column references that SQLite can evaluate down to a single result. Once you really understand expressions, you start seeing SQL differently — not as a rigid set of clause templates, but as a flexible language where you can compute, transform, and combine values almost anywhere. In this guide, I’ll walk through what expressions are, the different forms they take, and how to use them effectively throughout your SQLite queries.
What Is an Expression?
An expression in SQLite is anything that evaluates to a single value. This could be as simple as a literal number, or as complex as a nested combination of functions, operators, and subqueries. Expressions can appear in almost every part of a SQL statement: the SELECT list, WHERE clauses, ORDER BY clauses, GROUP BY, HAVING, CASE statements, and even inside other expressions.
Here are a few basic examples, ranging from simple to more complex:
5 + 3 -- a literal arithmetic expression
'Hello' || ' ' || 'World' -- a string concatenation expression
price * quantity -- a column-based arithmetic expression
UPPER(name) -- a function call expression
(SELECT MAX(salary) FROM employees) -- a scalar subquery expression
Every one of these evaluates down to a single value, which is the defining characteristic of an expression.
Literal Values
The simplest form of expression is a literal — a fixed value written directly into your SQL.
SELECT 42; -- integer literal
SELECT 3.14; -- real (floating point) literal
SELECT 'Hello, world'; -- text literal
SELECT X'48656C6C6F'; -- blob literal (hex-encoded)
SELECT NULL; -- the NULL literal
SELECT TRUE; -- boolean literal (stored internally as integer 1)
SELECT FALSE; -- boolean literal (stored internally as integer 0)
SQLite is dynamically typed (it uses what’s called “type affinity” rather than strict static typing per column), and literals are interpreted based on their syntax: unquoted numbers are numeric, single-quoted or double-quoted text is a string, and X'...' denotes a blob literal in hexadecimal form.
Arithmetic Expressions
SQLite supports the standard set of arithmetic operators, and you can use them in the SELECT list, WHERE clause, ORDER BY, and virtually anywhere else an expression is valid.
SELECT price + tax AS total_price FROM products;
SELECT quantity - reserved AS available FROM inventory;
SELECT price * quantity AS line_total FROM order_items;
SELECT total / count AS average FROM summary;
SELECT value % 10 AS remainder FROM numbers; -- modulo operator
A detail worth knowing: SQLite performs integer division when both operands are integers, meaning 7 / 2 evaluates to 3, not 3.5. If you need a true floating-point result, cast at least one operand to a real number:
SELECT 7 / 2; -- returns 3
SELECT 7.0 / 2; -- returns 3.5
SELECT CAST(7 AS REAL) / 2; -- returns 3.5
String Expressions
SQLite uses the || operator for string concatenation, which is standard SQL syntax (unlike MySQL, which traditionally uses CONCAT(), though SQLite also has || as its idiomatic form).
SELECT first_name || ' ' || last_name AS full_name FROM users;
You can combine concatenation with functions for more complex text transformations:
SELECT UPPER(first_name) || ' ' || LOWER(last_name) AS formatted_name FROM users;
SQLite includes a solid set of built-in string functions you’ll use constantly inside expressions: LENGTH(), UPPER(), LOWER(), SUBSTR(), TRIM(), REPLACE(), INSTR(), and more.
SELECT LENGTH(description) AS description_length FROM products;
SELECT SUBSTR(phone, 1, 3) AS area_code FROM contacts;
SELECT TRIM(name) AS clean_name FROM customers;
SELECT REPLACE(email, '@old-domain.com', '@new-domain.com') AS updated_email FROM users;
Comparison Expressions
Comparison expressions evaluate to a boolean-like result (internally represented as 1, 0, or NULL in SQLite, since it doesn’t have a true dedicated boolean type separate from integers).
salary > 50000
department = 'Sales'
hire_date <= '2020-01-01'
status != 'cancelled'
These are the building blocks of WHERE clauses, HAVING clauses, JOIN conditions, and CASE expressions — anywhere a true/false decision needs to be made.
Logical Expressions
Logical expressions combine boolean-producing expressions using AND, OR, and NOT.
(department = 'Sales' OR department = 'Marketing') AND salary > 60000
NOT is_deleted
status = 'active' AND (region = 'West' OR region = 'Central')
I’ve written a dedicated deep dive elsewhere specifically on AND/OR precedence and NULL interactions, since it’s a large enough topic on its own, but it’s worth remembering here: these are expressions just like any other, and they can be nested and combined as deeply as needed.
CASE Expressions
CASE is one of the most powerful and flexible expression types in SQL — it lets you build conditional logic directly inside a query, producing different output values depending on which condition matches.
There are two forms: the “searched” CASE, which evaluates a series of independent boolean conditions, and the “simple” CASE, which compares a single expression against multiple possible values.
-- Searched CASE
SELECT name,
CASE
WHEN salary >= 100000 THEN 'High'
WHEN salary >= 60000 THEN 'Medium'
ELSE 'Low'
END AS salary_tier
FROM employees;
-- Simple CASE
SELECT name,
CASE department
WHEN 'Engineering' THEN 'Tech'
WHEN 'Sales' THEN 'Revenue'
WHEN 'Marketing' THEN 'Revenue'
ELSE 'Other'
END AS division
FROM employees;
CASE expressions can appear anywhere a regular expression can — in the SELECT list, in WHERE, in ORDER BY, even nested inside other CASE expressions or function calls. I use them constantly for bucketing continuous values into categories, translating codes into human-readable labels, and building custom sort orders (as I covered in more depth in my ORDER BY guide).
Function Call Expressions
Functions are expressions that take zero or more argument expressions and return a single computed value. SQLite has a rich standard library of built-in scalar functions:
SELECT ABS(-15); -- 15
SELECT ROUND(3.14159, 2); -- 3.14
SELECT COALESCE(nickname, first_name); -- returns nickname if not NULL, otherwise first_name
SELECT IFNULL(discount, 0); -- returns discount if not NULL, otherwise 0
SELECT typeof(value); -- returns the storage class of a value ('integer', 'text', etc.)
SELECT date('now'); -- current date
SELECT datetime('now', '+1 day'); -- current datetime plus one day
COALESCE and IFNULL deserve special mention because they’re two of the most useful expression-level tools for handling missing data gracefully, right inside a query, without needing a separate CASE expression every time.
SELECT name, COALESCE(phone, email, 'No contact info') AS contact
FROM customers;
This returns the phone number if it exists; otherwise the email; otherwise a fallback string — evaluated left to right, returning the first non-NULL argument encountered.
Aggregate Function Expressions
Aggregate functions are a special category of function that operate across multiple rows (typically within a GROUP BY group) rather than a single row, collapsing them into one summary value.
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary, MAX(salary) AS top_salary
FROM employees
GROUP BY department;
COUNT(), SUM(), AVG(), MIN(), and MAX() are the standard set, and they can only be used in the SELECT list, HAVING clause, or ORDER BY of a query — never directly in a WHERE clause, since WHERE filters individual rows before aggregation happens.
Subquery Expressions
A subquery that returns a single value (one row, one column) can be used anywhere a regular expression is valid — this is called a “scalar subquery.”
SELECT name, salary,
salary - (SELECT AVG(salary) FROM employees) AS diff_from_average
FROM employees;
This computes, for every employee, how far their salary deviates from the company-wide average — all within a single SELECT statement, with the subquery acting as just another expression embedded in the SELECT list.
Subqueries can also be used as boolean expressions via EXISTS, or as set-membership expressions via IN:
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
SELECT name FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE region = 'West');
CAST Expressions and Type Conversion
SQLite’s dynamic typing system means values don’t always arrive in the exact type you need for a calculation or comparison. The CAST expression lets you explicitly convert a value’s type.
SELECT CAST('123' AS INTEGER); -- converts text to integer: 123
SELECT CAST(45 AS TEXT); -- converts integer to text: '45'
SELECT CAST('3.14' AS REAL); -- converts text to real: 3.14
SELECT CAST(order_total AS INTEGER); -- truncates decimal portion
This is genuinely important when you’re doing arithmetic on values that might have arrived as text (which happens often when importing data from CSV files or external sources without strict typing), since arithmetic operators on text values that don’t look like numbers won’t behave the way you expect.
Operator Precedence in Expressions
Just like in general programming languages, SQLite expressions follow operator precedence rules that determine the order operations are evaluated in, absent explicit parentheses. From highest to lowest precedence (roughly): unary operators (-, +, ~, NOT), multiplication/division/modulo (*, /, %), addition/subtraction (+, -), string concatenation (||), comparison operators, IS/IS NOT/IN/LIKE/GLOB/BETWEEN, AND, and finally OR at the lowest precedence.
SELECT 2 + 3 * 4; -- 14, not 20, because * happens before +
SELECT (2 + 3) * 4; -- 20, because parentheses force + to happen first
My general rule, especially in production code: whenever an expression mixes more than one type of operator and the order of evaluation genuinely matters to the result, I add explicit parentheses — not because I don’t know the precedence rules, but because it removes any doubt for whoever reads the query next.
Common Mistakes to Avoid
- Assuming integer division produces a decimal result. Cast at least one operand to REAL when you need fractional results.
- Forgetting that comparisons against NULL always evaluate to NULL, not true or false, which affects how expressions behave inside WHERE, CASE, and logical operators.
- Not using parentheses in mixed-operator expressions, relying on precedence rules that aren’t obvious to someone else reading the query later.
- Using aggregate functions where only a per-row expression is valid (like directly in a WHERE clause), instead of the correct HAVING clause or a subquery.
- Not casting text values before doing arithmetic on them, especially with data imported from external sources where type affinity might not be what you expect.
Best Practices
- Use COALESCE and IFNULL liberally to handle missing data gracefully within your expressions, rather than relying purely on application-layer fallback logic.
- Add explicit parentheses in any expression that mixes multiple operator types, even when you’re confident about the default precedence.
- Cast explicitly with CAST() whenever type ambiguity could affect the outcome of arithmetic or comparisons.
- Use CASE expressions to consolidate conditional logic directly into your queries, rather than pulling raw data and doing the branching in application code.
- Remember that scalar subqueries must return exactly one row and one column to be valid as an expression — verify this assumption when writing them.
Expressions are the connective tissue of every SQL query you’ll ever write in SQLite. Once you start recognizing them everywhere — not just as isolated syntax rules tied to specific clauses, but as a general-purpose way to compute and transform values — you’ll find yourself writing shorter, more powerful queries that do more work directly in the database, rather than in your application code.
Frequently Asked Questions
Does SQLite evaluate expressions the same way every time, or can results vary? For pure, deterministic expressions (arithmetic, string functions, comparisons), results are entirely consistent given the same inputs. Non-deterministic expressions — like RANDOM() or datetime('now') — will naturally produce different results across different executions, since they’re explicitly designed to reflect changing state (randomness or the current time) rather than a fixed computation.
Can an expression reference a column that doesn’t exist in the current table? No — SQLite validates column references at parse/prepare time, and referencing a nonexistent column raises a “no such column” error immediately, rather than silently evaluating to NULL.
What’s the difference between an expression and a clause? A clause (like WHERE, SELECT, ORDER BY) is a structural part of a SQL statement — it defines where something happens in the query. An expression is what gets evaluated within that structure. Clauses are made up of one or more expressions; expressions themselves aren’t clauses. Thinking of it this way helped me a lot early on: clauses are the skeleton of a query, and expressions are what fill in the actual computation.
Can I define reusable expressions, like a variable, within a single query? Not directly as a “variable” in the programming-language sense, but SQLite supports Common Table Expressions (CTEs) via the WITH clause, which let you name and reuse a computed result set (not quite a single expression, but a similar idea at the query level).
WITH avg_salary AS (
SELECT AVG(salary) AS value FROM employees
)
SELECT name, salary, salary - (SELECT value FROM avg_salary) AS diff
FROM employees;
Generated columns are another related feature — they let you define an expression once at the schema level, and have it automatically computed for every row without needing to repeat the expression in every query:
CREATE TABLE order_items (
quantity INTEGER,
unit_price REAL,
line_total REAL GENERATED ALWAYS AS (quantity * unit_price) STORED
);
Date and Time Expressions
SQLite doesn’t have a dedicated native date/time storage type — dates are typically stored as ISO8601 text strings, Julian day real numbers, or Unix timestamps as integers. Regardless of storage format, SQLite provides a rich set of date/time functions that behave as expressions, and I use them constantly.
SELECT date('now'); -- today's date
SELECT datetime('now'); -- current date and time
SELECT date('now', '+7 days'); -- one week from today
SELECT strftime('%Y-%m', order_date) AS month FROM orders; -- extract year-month
SELECT julianday('2024-12-25') - julianday('now') AS days_until_christmas;
strftime() in particular is one of the most powerful expression-level tools in SQLite for date manipulation, letting you format, extract, and compute against date values with a huge amount of flexibility, all through format-string-based expressions rather than needing separate dedicated functions for every possible date operation.
Aggregate Expressions with FILTER
A less commonly known but genuinely useful feature: SQLite (since version 3.30.0) supports the FILTER clause on aggregate function expressions, letting you conditionally include only certain rows within a single aggregate calculation, without needing a separate CASE-wrapped SUM.
SELECT
department,
COUNT(*) AS total_employees,
COUNT(*) FILTER (WHERE salary > 80000) AS high_earners
FROM employees
GROUP BY department;
This is functionally similar to writing SUM(CASE WHEN salary > 80000 THEN 1 ELSE 0 END), but reads more clearly and expresses the conditional intent more directly as part of the aggregate expression itself.
Expression Indexes
Because expressions are so central to how SQLite processes data, it’s worth knowing that you can build indexes not just on raw columns, but on the result of an expression — genuinely useful when you frequently filter or sort by a computed value.
CREATE INDEX idx_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'someone@example.com';
Without this expression index, filtering on LOWER(email) would force a full table scan, since a normal index on the raw email column can’t help match against a transformed value. Expression indexes close that gap, letting SQLite use an index even when your WHERE clause wraps a column in a function.
A Practical Debugging Tip for Complex Expressions
When I’m building a complicated expression — especially one involving nested CASE statements, COALESCE chains, or multiple subqueries — I almost always test it standalone first, outside the full query, using a simple SELECT <expression>; with no FROM clause, or against a tiny sample of rows using LIMIT. This isolates the expression’s logic from everything else going on in the query, making it dramatically easier to spot a mistake in the computation itself versus a mistake somewhere else in the surrounding SQL.
SELECT CASE WHEN 5 > 3 THEN 'yes' ELSE 'no' END; -- quick sanity check of CASE logic
This habit of testing expressions in isolation before embedding them into a larger, more complex query has saved me a substantial amount of debugging time over the years, and it’s a technique I’d recommend to anyone still building intuition for how SQLite expressions actually evaluate.