SQL Language Expressions (2025)

expr:

SQLite understands these operators, listed in precedence¹ order
(top to bottom / highest to lowest):

1. Operators shown within the same table cell share precedence.
   
2. "[expr]" denotes operand locations for non-binary operators.
   Operators with no "[expr]" adjunct are binary and left-associative.
   
3. The COLLATE clause (with its collation-name) acts as a single postfix operator.
   
4. The ESCAPE clause (with its escape character) acts as a single postfix operator.
   It can only bind to a preceding [expr] LIKE [expr] expression.
   
5. Each keyword in (BETWEEN IN GLOB LIKE MATCH REGEXP) may be prefixed
   by NOT, retaining the bare operator's precedence and associativity.

The COLLATE operator is a unary postfixoperator that assigns a collating sequence to an expression.The collating sequence set by the COLLATE operator overrides thecollating sequence determined by the COLLATE clause in a tablecolumn definition.See the detailed discussion on collating sequencesin the Datatype In SQLite3 document for additional information.

The unary operator + is a no-op. It can be appliedto strings, numbers, blobs or NULL and it always returns a resultwith the same value as the operand.

Note that there are two variations of the equals and not equalsoperators. Equals can be either= or ==.The not-equal operator can be either!= or <>.The || operator is "concatenate" - it joins togetherthe two strings of its operands.The -> and ->> operators are "extract";they extract the RHS component from the LHS.For an example, seeJSON subcomponent extraction.

The % operator casts both of its operands to typeINTEGER and then computes the remainder after dividing the left integerby the right integer. The other arithmetic operators perform integerarithmetic if both operands are integers and no overflow would result,or floating point arithmetic, per IEEE Standard 754, if either operandis a real value or integer arithmetic would produce an overflow.Integer divide yields an integer result, truncated toward zero.

The result of any binary operator is either a numeric value orNULL, except for the || concatenation operator,and the -> and ->> extract operatorswhich can return values of any type.

All operators generally evaluate to NULL when any operand is NULL,with specific exceptions as stated below. This is in accordance withthe SQL92 standard.

When paired with NULL:
AND evaluates to 0 (false) whenthe other operand is false; and
OR evaluates to 1 (true)when the other operand is true.

The IS and IS NOT operators worklike = and != except when one or both of theoperands are NULL. In this case, if both operands are NULL, then theIS operator evaluates to 1 (true) and the IS NOT operator evaluatesto 0 (false). If one operand is NULL and the other is not, then theIS operator evaluates to 0 (false) and the IS NOT operator is 1 (true).It is not possible for an IS or IS NOT expression to evaluate to NULL.

The IS NOT DISTINCT FROM operator is an alternative spellingfor the IS operator.Likewise, the IS DISTINCT FROM operator means the same thingas IS NOT. Standard SQL does not support the compact IS and IS NOTnotation. Those compact forms are an SQLite extension. You have to usethe prolix and much less readable IS NOT DISTINCT FROM andIS DISTINCT FROM operators on other SQL database engines.

A literal value represents a constant.Literal values may be integers, floating point numbers, strings,BLOBs, or NULLs.

The syntax for integer and floating point literals (collectively"numeric literals") is shown by the following diagram:

numeric-literal:

If a numeric literal has a decimal point or an exponentiationclause or if it is less than -9223372036854775808 orgreater than 9223372036854775807, then it is a floating point literal.Otherwise is it is an integer literal.The "E" character that begins the exponentiationclause of a floating point literal can be either upper or lower case.The "." character is always usedas the decimal point even if the locale setting specifies "," forthis role - the use of "," for the decimal point would result insyntactic ambiguity.

Beginning in SQLite version 3.46.0 (2024-05-23), a single extra underscore ("_")character can be added between any two digits. The underscores are purelyfor human readability and are ignored by SQLite.

Hexadecimal integer literals follow the C-language notation of"0x" or "0X" followed by hexadecimal digits.For example, 0x1234 means the same as 4660and 0x8000000000000000 means the same as -9223372036854775808. Hexadecimal integer literals are interpreted as 64-bittwo's-complement integers and are thus limitedto sixteen significant digits of precision.Support for hexadecimal integers was added to SQLiteversion 3.8.6 (2014-08-15).For backwards compatibility, the "0x" hexadecimal integernotation is only understood by the SQL language parser, not by thetype conversions routines.String variables thatcontain text formatted like hexadecimal integers are notinterpreted as hexadecimal integers when coercing the string valueinto an integer due to a CAST expression or for a column affinitytransformation or prior to performing a numeric operation or forany other run-time conversions. When coercing astring value in the format of a hexadecimal integer into an integervalue, the conversion process stops when the 'x' character is seenso the resulting integer value is always zero.SQLite only understands the hexadecimal integer notation when itappears in the SQL statement text, not when it appears aspart of the content of the database.

A string constant is formed by enclosing thestring in single quotes ('). A single quote within the string canbe encoded by putting two single quotes in a row - as in Pascal.C-style escapes using the backslash character are not supported becausethey are not standard SQL.

BLOB literals are string literals containing hexadecimal data andpreceded by a single "x" or "X" character. Example: X'53514C697465'

A literal value can also be the token "NULL".

A "variable" or "parameter" tokenspecifies a placeholder in the expression for avalue that is filled in at runtime using thesqlite3_bind() family of C/C++ interfaces.Parameters can take several forms:

?NNN		A question mark followed by a number NNN holds a spot for theNNN-th parameter. NNN must be between 1 and SQLITE_MAX_VARIABLE_NUMBER.
?		A question mark that is not followed by a number creates a parameterwith a number one greater than the largest parameter number already assigned.If this means the parameter number is greater thanSQLITE_MAX_VARIABLE_NUMBER, it is an error.This parameter format is provided for compatibility with other databaseengines. But because it is easy to miscount the question marks, theuse of this parameter format is discouraged. Programmers are encouragedto use one of the symbolic formats below or the ?NNN format above instead.
:AAAA		A colon followed by an identifier name holds a spot for anamed parameter with the name :AAAA.Named parameters are also numbered. The number assigned is one greater thanthe largest parameter number already assigned. If this means the parameterwould be assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it isan error. To avoid confusion, it is best to avoid mixing named and numberedparameters.
@AAAA		An "at" sign works exactly like a colon, except that the name ofthe parameter created is @AAAA.
$AAAA		A dollar-sign followed by an identifier name also holds a spot for a namedparameter with the name $AAAA. The identifier name in this case can includeone or more occurrences of "::" and a suffix enclosed in "(...)" containingany text at all. This syntax is the form of a variable name in theTcl programming language. The presenceof this syntax results from the fact that SQLite is really aTcl extension that has escaped into the wild.

Parameters that are not assigned values usingsqlite3_bind() are treatedas NULL. The sqlite3_bind_parameter_index() interface can be usedto translate a symbolic parameter name into its equivalent numeric index.

The maximum parameter number is set at compile-time bythe SQLITE_MAX_VARIABLE_NUMBER macro. An individual database connectionD can reduce its maximum parameter number below the compile-time maximumusing the sqlite3_limit(D, SQLITE_LIMIT_VARIABLE_NUMBER,...) interface.

The LIKE operator does a pattern matching comparison. The operandto the right of the LIKE operator contains the pattern and the left handoperand contains the string to match against the pattern.A percent symbol ("%") in the LIKE pattern matches anysequence of zero or more characters in the string. An underscore("_") in the LIKE pattern matches any single character in thestring. Any other character matches itself or its lower/upper caseequivalent (i.e. case-insensitive matching).Important Note: SQLite onlyunderstands upper/lower case for ASCII characters by default. TheLIKE operator is case sensitive by default for unicode characters that arebeyond the ASCII range. For example,the expression 'a'LIKE'A'is TRUE but 'æ'LIKE'Æ' is FALSE.The ICU extension to SQLite includes an enhanced version of theLIKE operator that does case folding across all unicode characters.

If the optional ESCAPE clause is present, then the expressionfollowing the ESCAPE keyword must evaluate to a string consisting ofa single character. This character may be used in the LIKE patternto include literal percent or underscore characters. The escapecharacter followed by a percent symbol (%), underscore (_), or a secondinstance of the escape character itself matches aliteral percent symbol, underscore, or a single escape character,respectively.

The infix LIKE operator is implemented by calling theapplication-defined SQL functions like(Y,X) orlike(Y,X,Z).

The LIKE operator can be made case sensitive using thecase_sensitive_like pragma.

The GLOB operator is similar to LIKE but uses the Unixfile globbing syntax for its wildcards. Also, GLOB is casesensitive, unlike LIKE. Both GLOB and LIKE may be preceded bythe NOT keyword to invert the sense of the test. The infix GLOBoperator is implemented by calling the functionglob(Y,X) and can be modified by overridingthat function.

The REGEXP operator is a special syntax for the regexp()user function. No regexp() user function is defined by defaultand so use of the REGEXP operator will normally result in anerror message. If an application-defined SQL function named "regexp"is added at run-time, then the "X REGEXP Y" operator willbe implemented as a call to "regexp(Y,X)".

The MATCH operator is a special syntax for the match()application-defined function. The default match() function implementationraises an exception and is not really useful for anything.But extensions can override the match() function with morehelpful logic.

The extract operators act as a special syntax for functions"->"() and "->>"(). Default implementations for these functionsperform JSON subcomponent extraction,but extensions can override them for other purposes.

The BETWEEN operator is logically equivalent to a pair of comparisons."x BETWEEN y AND z" isequivalent to"x>=y AND x<=z" exceptthat with BETWEEN, the x expression is only evaluated once.

A CASE expression serves a role similar to IF-THEN-ELSE in otherprogramming languages.

The optional expression that occurs in between the CASE keyword and thefirst WHEN keyword is called the "base" expression. There are twofundamental formsof the CASE expression: those with a base expression and those without.

In a CASE without a base expression, each WHEN expression is evaluatedand the result treated as a boolean, starting with the leftmost and continuingto the right. The result of the CASE expression is the evaluation of the THENexpression that corresponds to the first WHEN expression that evaluates totrue. Or, if none of the WHEN expressions evaluate to true, the result ofevaluating the ELSE expression, if any. If there is no ELSE expression andnone of the WHEN expressions are true, then the overall result is NULL.

A NULL result is considered untrue when evaluating WHEN terms.

In a CASE with a base expression, the base expression is evaluated justonce and the result is compared against the evaluation of each WHENexpression from left to right. The result of the CASE expression is theevaluation of the THEN expression that corresponds to the first WHENexpression for which the comparison is true. Or, if none of the WHENexpressions evaluate to a value equal to the base expression, the resultof evaluating the ELSE expression, if any. If there is no ELSE expression andnone of the WHEN expressions produce a result equal to the base expression,the overall result is NULL.

When comparing a base expression against a WHEN expression, the samecollating sequence, affinity, and NULL-handling rules apply as if thebase expression and WHEN expression are respectively the left- andright-hand operands of an = operator.

If the baseexpression is NULL then the result of the CASE is always the resultof evaluating the ELSE expression if it exists, or NULL if it does not.

Both forms of the CASE expression use lazy, or short-circuit,evaluation.

The only difference between the following two CASE expressions is thatthe x expression is evaluated exactly once in the first example butmight be evaluated multiple times in the second:

The built-in iif(x,y,z) SQL function is logicallyequivalent to "CASE WHEN x THEN y ELSE z END". The iif() functionis found in SQL Server and is included in SQLite for compatibility.Some developers prefer the iif() function because it is moreconcise.

The IN and NOT IN operators take an expression on theleft and a list of values or a subquery on the right.When the right operand of an IN or NOT IN operator is a subquery, thesubquery must have the same number of columns as there are columns inthe row value of the left operand. The subquery on theright of an IN or NOT IN operator must be a scalar subquery if the leftexpression is not a row value expression.If the right operand of an IN or NOT IN operator is a list of values,each of those values must be scalars and the left expression must alsobe a scalar.The right-hand side of an IN or NOT IN operator can be atable name or table-valued function name in whichcase the right-hand side is understood to be subquery ofthe form "(SELECT * FROM name)".When the right operand is an empty set, the result of IN is false and theresult of NOT IN is true, regardless of the left operand and even if theleft operand is NULL.

The result of an IN or NOT IN operator is determined by the followingmatrix:

Left operand is NULL	Right operand contains NULL	Right operand is an empty set	Left operand found within right operand	Result of IN operator	Result of NOT IN operator
no	no	no	no	false	true
does not matter	no	yes	no	false	true
no	does not matter	no	yes	true	false
no	yes	no	no	NULL	NULL
yes	does not matter	no	does not matter	NULL	NULL

Note that SQLite allows the parenthesized list of scalar values onthe right-hand side of an IN or NOT IN operator to be an empty list butmost other SQL database engines and the SQL92 standard requirethe list to contain at least one element.

A column name can be any of the names defined in the CREATE TABLEstatement or one of the following special identifiers: "ROWID","OID", or "_ROWID_".The three special identifiers describe theunique integer key (the rowid) associated with everyrow of every table and so are not available on WITHOUT ROWID tables.The special identifiers only refer to the row key if the CREATE TABLEstatement does not define a real column with the same name.The rowid can be used anywhere a regularcolumn can be used.

The EXISTS operator always evaluates to one of the integer values 0and 1. If executing the SELECT statement specified as the right-handoperand of the EXISTS operator would return one or more rows, then theEXISTS operator evaluates to 1. If executing the SELECT would returnno rows at all, then the EXISTS operator evaluates to 0.

The number of columns in each row returned by the SELECT statement(if any) and the specific values returned have no effect on the resultsof the EXISTS operator. In particular, rows containing NULL values arenot handled any differently from rows without NULL values.

A SELECT statement enclosed in parentheses is a subquery.All types of SELECT statement, includingaggregate and compound SELECT queries (queries with keywords likeUNION or EXCEPT) are allowed as scalar subqueries.The value of a subquery expression is the first row of the resultfrom the enclosed SELECT statement.The value of a subquery expression is NULL if the enclosedSELECT statement returns no rows.

A subquery that returns a single column is a scalar subquery and canbe used most anywhere.A subquery that returns two or more columns is a row valuesubquery and can only be used as an operand of a comparison operator or asthe value in an UPDATE SET clause whose column name list has the same size.

A SELECT statement used as either a scalar subquery or as theright-hand operand of an IN, NOT IN or EXISTS expression may containreferences to columns in the outer query. Such a subquery is known asa correlated subquery. A correlated subquery is reevaluated each timeits result is required. An uncorrelated subquery is evaluated only onceand the result reused as necessary.

A CAST expression of the form "CAST(expr AS type-name)"is used to convert the value of expr toa different storage class specified by type-name.A CAST conversion is similar to the conversion that takesplace when a column affinity is applied to a value except that withthe CAST operator the conversion always takes place even if the conversionlossy and irreversible, whereas column affinity only changes the data typeof a value if the change is lossless and reversible.

If the value of expr is NULL, then the result of the CASTexpression is also NULL. Otherwise, the storage class of the resultis determined by applying the rules for determining column affinity tothe type-name.

Affinity of type-name	Conversion Processing
NONE	Casting a value to a type-name with no affinity causes the value to be converted into a BLOB. Casting to a BLOB consists of first casting the value to TEXT in the encoding of the database connection, then interpreting the resulting byte sequence as a BLOB instead of as TEXT.
TEXT	To cast a BLOB value to TEXT, the sequence of bytes that make up the BLOB is interpreted as text encoded using the database encoding. Casting an INTEGER or REAL value into TEXT renders the value as if via sqlite3_snprintf() except that the resulting TEXT uses the encoding of the database connection.
REAL	When casting a BLOB value to a REAL, the value is first converted to TEXT. When casting a TEXT value to REAL, the longest possible prefix of the value that can be interpreted as a real number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value are ignored when converging from TEXT to REAL. If there is no prefix that can be interpreted as a real number, the result of the conversion is 0.0.
INTEGER	When casting a BLOB value to INTEGER, the value is first converted to TEXT. When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value when converting from TEXT to INTEGER are ignored. If there is no prefix that can be interpreted as an integer number, the result of the conversion is 0. If the prefix integer is greater than +9223372036854775807 then the result of the cast is exactly +9223372036854775807. Similarly, if the prefix integer is less than -9223372036854775808 then the result of the cast is exactly -9223372036854775808. When casting to INTEGER, if the text looks like a floating point value with an exponent, the exponent will be ignored because it is no part of the integer prefix. For example, "CAST('123e+5' AS INTEGER)" results in 123, not in 12300000. The CAST operator understands decimal integers only — conversion of hexadecimal integers stops at the "x" in the "0x" prefix of the hexadecimal integer string and thus result of the CAST is always zero. A cast of a REAL value into an INTEGER results in the integer between the REAL value and zero that is closest to the REAL value. If a REAL is greater than the greatest possible signed integer (+9223372036854775807) then the result is the greatest possible signed integer and if the REAL is less than the least possible signed integer (-9223372036854775808) then the result is the least possible signed integer. Prior to SQLite version 3.8.2 (2013-12-06), casting a REAL value greater than +9223372036854775807.0 into an integer resulted in the most negative integer, -9223372036854775808. This behavior was meant to emulate the behavior of x86/x64 hardware when doing the equivalent cast.
NUMERIC	Casting a TEXT or BLOB value into NUMERIC yields either an INTEGER or a REAL result. If the input text looks like an integer (there is no decimal point nor exponent) and the value is small enough to fit in a 64-bit signed integer, then the result will be INTEGER. Input text that looks like floating point (there is a decimal point and/or an exponent) and the text describes a value that can be losslessly converted back and forth between IEEE 754 64-bit float and a 51-bit signed integer, then the result is INTEGER. (In the previous sentence, a 51-bit integer is specified since that is one bit less than the length of the mantissa of an IEEE 754 64-bit float and thus provides a 1-bit of margin for the text-to-float conversion operation.) Any text input that describes a value outside the range of a 64-bit signed integer yields a REAL result. Casting a REAL or INTEGER value to NUMERIC is a no-op, even if a real value could be losslessly converted to an integer.

Note that the result from casting any non-BLOB value into aBLOB and the result from casting any BLOB value into a non-BLOB valuemay be different depending on whether the database encoding is UTF-8,UTF-16be, or UTF-16le.

The SQL language features several contexts where an expression isevaluated and the result converted to a boolean (true or false) value. Thesecontexts are:

• the WHERE clause of a SELECT, UPDATE or DELETE statement,
• the ON or USING clause of a join in a SELECT statement,
• the HAVING clause of a SELECT statement,
• the WHEN clause of an SQL trigger, and
• the WHEN clause or clauses of some CASE expressions.

To convert the results of an SQL expression to a boolean value, SQLitefirst casts the result to a NUMERIC value in the same way as aCAST expression. A numeric zero value (integer value 0 or realvalue 0.0) is considered to be false. A NULL value is still NULL.All other values are considered true.

For example, the values NULL, 0.0, 0, 'english' and '0' are all consideredto be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered tobe true.

Beginning with SQLite 3.23.0 (2018-04-02), SQLite recognizes theidentifiers "TRUE" and "FALSE" as boolean literals, if and only if thoseidentifiers are not already used for some other meaning. If there alreadyexists columns or tables or other objects named TRUE or FALSE, then forthe sake of backwards compatibility, the TRUE and FALSE identifiers referto those other objects, not to the boolean values.

The boolean identifiers TRUE and FALSE are usually just aliases forthe integer values 1 and 0, respectively. However, if TRUE or FALSEoccur on the right-hand side of an IS operator, then the IS operatorevaluates the left-hand operand as a boolean value and returns an appropriateanswer.

SQLite supports many simple, aggregate,and windowSQL functions. For presentation purposes, simple functions are furthersubdivided into core functions, date-time functions,math functions, and JSON functions.Applications can add new functions, written in C/C++, using thesqlite3_create_function() interface.

The main expression bubble diagram above shows a single syntax forall function invocations. But this is merely to simplify the expressionbubble diagram. In reality, each type of function has a slightly differentsyntax, shown below. The function invocation syntax shown in the mainexpression bubble diagram is the union of the three syntaxes shown here:

simple-function-invocation:

aggregate-function-invocation:

window-function-invocation:

The OVER clause is required for window functions and is prohibitedotherwise. The DISTINCT keyword and the ORDER BY clause is only allowedin aggregate functions.The FILTER clause may not appear on a simple function.

It is possible to have an aggregate function with the same name as asimple function, as long as the number of arguments for the two forms of thefunction are different. For example, the max() function with asingle argument is an aggregate and the max() function with two or morearguments is a simple function. Aggregate functions can usually alsobe used as window functions.

This page last modified on 2024-06-02 10:08:16 UTC