Before getting into specific commands in SQL, it is worth looking at the general language structure. Like most languages, SQL has a fairly complete expression syntax that can be used to define command parameters. A more detailed description of the expression support can be found in Appendix D.
SQL consists of a number of different commands,
CREATE TABLE or
INSERT. These commands are issued and
processed one at a time. Each command implements a different action or
feature of the database system.
Although it is customary to use all capital letters for SQL commands and keywords, SQL is a case-insensitive language. All commands and keywords are case insensitive, as are identifiers (such as table names and column names).
Identifiers must be given as literals. If
necessary, identifiers can be enclosed in the standards compliant
" ") to allow the
inclusion of spaces or other nonstandard characters in an identifier.
SQLite also allows identifiers to be enclosed in square brackets (
[ ]) or back
` `) for
compatibility with other popular database products. SQLite reserves
the use of any identifier that uses
sqlite_ as a prefix.
SQL is whitespace insensitive, including line
breaks. Individual statements are separated by a semicolon. If you’re using an interactive application,
such as the
command-line tool, then you’ll
need to use a semicolon to indicate the end of a statement. The
semicolon is not strictly required for single statements, however, as
it is properly a statement separator and not a statement terminator.
When passing SQL commands into the programming API, the semicolon is
not required unless you are passing more than one command statement
within a single string.
As with most languages, numeric literals are represented bare. Both integer (
453) and real (rational) numbers
43.23) are recognized, as is
exponent-style scientific notation (
9.745e-6). In order to avoid ambiguities in the
parser, SQLite requires that the decimal point is always represented
as a period (
.), regardless of the
current internationalization setting.
Text literals are enclosed in single quotes (
' '). To represent a
string literal that includes a single quote character, use two single
quotes in a row (
backslash escapes (
) are not part of the SQL standard and are not supported by SQLite.
BLOB literals (binary data) can be represented as an
X) followed by a string literal of hexadecimal
In general, expressions can be used any place a literal data value is allowed. Expressions can include both mathematical statements, as well as functions. Function-calling syntax is similar to most other computer languages, utilizing the name of the function, followed by a list of parameters enclosed in parentheses. Expressions can be grouped into subexpressions using parentheses.
If an expression is evaluated in the context of a row (such as a filtering expression), the value of a row element can be extracted by naming the column. You may have to qualify the column name with a table name or alias. If you’re using cross-database queries, you may also have to specify which database you’re referring to. The syntax is:
If no database name is given, it is assumed you’re
referring to the
main database on
the default connection. If the table name/alias is also omitted, the
system will make a best-guess using just the column name, but will
return an error if the name is ambiguous.
SQL allows any value to be assigned a NULL. NULL is not a value in itself (SQLite actually implements it as a unique valueless type), but is used as a marker or flag to represent unknown or missing data. The thought is that there are times when values for a specific row element may not be available or may not be applicable.
A NULL may not be a value, but it can be assigned
to data elements that normally have values, and can therefore show up
in expressions. The problem is that NULLs don’t interact well with
other values. If a NULL represents an unknown that might be any
possible value, how can we know if the expression
NULL > 3 is true or
To deal with this problem, SQL must employ a concept called three-valued logic. Three-valued logic is often abbreviated TVL or 3VL, and is more formally known as ternary logic. 3VL essentially adds an “unknown” state to the familiar true/false Boolean logic system.
3VL also dictates how comparisons work. For
example, any equality check (
involving a NULL will evaluate to NULL, including
NULL = NULL. Remember that NULL is not
a value, it is a flag for the unknown, so the expression
NULL = NULL is asking, “Does this
unknown equal that unknown?” The only practical answer is, “That is
unknown.” It might, but it might
not. Similar rules apply to greater-than, less-than, and other
If you’re having trouble resolving an expression,
just remember that a NULL marks an unknown or unresolved value. This
is why the expression
NULL is false, but
NULL is NULL. In the case of the first expression, the
NULL can be replaced by either true or false without altering the
expression result. That isn’t true of the second expression, where the
outcome is unknown (in other words, NULL) because the output depends
on the unknown input.
These adjust the sign of a value. The “-” operator flips the sign of the value, effectively multiplying it by
-1.0. The “
+” operator is essentially a no-op, leaving a value with the same sign it previously had. It does not make negative values positive.
+ - * / %
| & << >>
< <= => >
= == != <>
Equality test operators. Both “
=” and “
==” will test for equality, while both “
!=” and “
<>” test for inequality. Being logic operators, these tests are subject to SQL’s 3VL regarding NULLs. Specifically,
value = NULLwill always return NULL.
IN LIKE GLOB MATCH REGEXP
These five keywords are logic operators, returning, true, false, or NULL state. See Appendix D for more specifics on these operators.
In addition to these basics, SQL supports a number of specific expression operations. For more information on these and any SQL-specific expressions, see Appendix D.
 Unless otherwise specified, case insensitivity only applies to ASCII characters. That is, characters represented by values less than 128.