This appendix lists the SQL commands and syntax that are supported by SQLite. SQL statements consist of a single command and any required parameters. Command statements are separated by a semicolon. Technically, standalone statements do not need to be terminated with a semicolon, but most interactive environments require the use of a semicolon to indicate that the current command statement is complete and should be executed. For example, the C API sqlite3_exec() does not require that command statements end with a semicolon, but interactive use of sqlite3 requires ending each statement with a semicolon.

In most situations where a table name is called for, a view name can be used instead. As noted in the syntax diagrams, in most instances where any object identifier is used (table name, view name, etc.), the name can be qualified with a logical database name to prevent any ambiguity between objects in different databases that share a similar name (see ATTACH DATABASE in this appendix). If the object is unqualified, it will be searched for in the temp database, followed by the main database, followed by each attached database, in order. If an unqualified identifier appears in a CREATE statement, the object will be created in the main database, unless the statement contains some type of CREATE TEMPORARY syntax. Object identifiers that use nonstandard characters must be quoted. See Basic Syntax for more info.

The SELECT, UPDATE, and DELETE commands contain clauses that are used to define search criteria on table rows. These table references can include the nonstandard phrases INDEXED BY or NOT INDEXED, to indicate whether the query optimizer should (or should not) attempt to use an index to satisfy the search condition. These extensions are included in SQLite to assist with testing, debugging, and hand-tuning queries. Their use in production code is not recommended, and therefore they are not included in the syntax diagrams or command explanations found in this appendix. For more information, see the SQLite website (http://www.sqlite.org/lang_indexedby.html).

Finally, be aware that the syntax diagrams presented with each command should not be taken as the definitive specification for the full command syntax. Some rarely used, nonstandard syntax (such as the INDEXED BY extension discussed in the previous paragraph) are not included in these diagrams. Similarly, there are possible syntax combinations that the diagrams will indicate are possible, but do not actually form logical statements. For example, according to the syntax diagrams, a JOIN operator can contain both a prefixed NATURAL condition, as well as a trailing ON or USING condition. This isn’t possible in practice, as a join is limited to only one type of condition. While it would have been possible to present the diagram with only the allowed syntax, the diagram would have become much larger and much more complex. In such situations, it was decided that making the diagram easy to understand was more important than making it walk an absolute line on what was allowed or not allowed. Thankfully, such situations are reasonably rare. Just don’t assume that because the parser can parse it means that the command makes sense to the database engine.