Table of Contents for

Using SQLite

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').

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.

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:

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.

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.

SQLite supports the following unary prefix operators:

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

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.