Using SQLite

General Syntax

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.

Basic Syntax

SQL consists of a number of different commands, such as 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^[1] 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 double-quotes (" ") 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 ticks (` `) 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 sqlite3 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.

Single-line comments start with a double dash (--) and go to the end of the line. SQL also supports multi-line comments using the C comment syntax (/* */).

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 (publisher = 'O''Reilly'). C-style 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 (or X) followed by a string literal of hexadecimal characters (x'A554E59C').

Warning

Text literals use single quotes. Double quotes are reserved for identifiers (table names, columns, etc.). C-style backslash escapes are not part of the SQL standard.

SQL statements and expressions frequently contain lists. A comma is used as the list separator. SQL does not allow for a trailing comma following the last item of a list.

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:

[[database_name.]table_name.]column_name

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.

Three-Valued Logic

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 false?

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.

Here are the truth tables for the 3VL operators NOT, AND, and OR:

Value	NOT Value
True	False
False	True
NULL	NULL

3VL AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

3VL OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

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 comparisons.

Warning

You cannot use the equality operator (=) to test for NULLs. You must use the IS NULL operator.

If you’re having trouble resolving an expression, just remember that a NULL marks an unknown or unresolved value. This is why the expression False AND NULL is false, but True AND 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.

Simple Operators

SQLite supports the following unary prefix operators:

- +

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.

~

As in the C language, the “~” operator performs a bit-wise inversion. This operator is not part of the SQL standard.

NOT

The NOT operator reverses a Boolean expression using 3VL.

There are also a number of binary operators. They are listed here in descending precedence.

||

String concatenation. This is the only string concatenation operator recognized by the SQL standard. Many other database products allow “+” to be used for concatenation, but SQLite does not.

+ - * / %

Standard arithmetic operators for addition, subtraction, multiplication, division, and modulus (remainder).

| & << >>

The bitwise operators or, and, and shift-high/shift-low, as found in the C language. These operators are not part of the SQL standard.

< <= => >

Comparison test operators. Again, just as in the C language we have less-than, less-than or equal, greater-than or equal, and greater than. These operators are subject to SQL’s 3VL regarding NULLs.

= == != <>

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 = NULL will 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.

AND OR

Logical operators. Again, they are subject to SQL’s 3VL.

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.

---

^[1] Unless otherwise specified, case insensitivity only applies to ASCII characters. That is, characters represented by values less than 128.