Syntax

The SQL accepted by SQLite is the SQL of ISO/IEC 9075 with a small, identifiable set of departures, the most consequential of which is the engine’s permissive treatment of identifiers and quoted strings. This page documents the surface of SQLite SQL — what counts as a statement, how identifiers are formed, how comments are written, and the conventions of presentation that the rest of this volume observes.

Statements

A statement is terminated by a semicolon. A script is a sequence of statements. The sqlite3 command-line shell executes a statement when the semicolon is read, which is why a multi-line CREATE TABLE is held until its closing semicolon. The C API takes statements one at a time through sqlite3_prepare_v2; the convenience function sqlite3_exec accepts a script and runs each statement in sequence.

CREATE TABLE customer (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO customer (name) VALUES ('Ada Lovelace');
SELECT * FROM customer;

The SQL grammar is described formally on the SQLite website using railroad diagrams and a Backus–Naur-style notation; the official syntax reference at sqlite.org/lang.html enumerates every statement. The principal categories are:

Category	Statements
Data definition (DDL)	CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE TRIGGER, CREATE VIRTUAL TABLE, ALTER TABLE, DROP TABLE, DROP INDEX, DROP VIEW, DROP TRIGGER
Data manipulation (DML)	INSERT, UPDATE, DELETE, SELECT
Transaction control	BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE
Engine configuration	PRAGMA, ATTACH DATABASE, DETACH DATABASE, VACUUM, ANALYZE
Diagnostic prefixes	EXPLAIN, EXPLAIN QUERY PLAN

There is no SQLite equivalent of the data-control statements GRANT and REVOKE: SQLite has no concept of a database user, and access control is the responsibility of the host application.

Keywords and case

Keywords are case-insensitive: SELECT, select, and Select are interchangeable. Conventional style uses uppercase for keywords and lowercase for identifiers, and this volume follows that convention. The list of reserved words — words that cannot be used as identifiers without quoting — is given in the SQLite documentation at sqlite.org/lang_keywords.html and changes only between major releases.

SELECT id, name FROM customer WHERE id = 1;
select id, name from customer where id = 1;   -- equivalent

Identifier matching is also case-insensitive by default for the ASCII letters, so SELECT * FROM Customer and SELECT * FROM customer refer to the same table. The matching of non-ASCII letters is exact by default; the PRAGMA case_sensitive_like setting controls case-folding for LIKE, and the per-database collation determines case-folding for comparisons in general (see Operators).

Identifiers and quoting

An identifier is a name for a database object — a table, column, index, view, trigger, or virtual table. An unquoted identifier consists of an ASCII letter or underscore followed by zero or more ASCII letters, digits, or underscores; identifiers beginning with a digit or containing other characters must be quoted.

SQLite recognises four identifier-quoting forms, three of which are non-standard concessions to compatibility with other database engines:

Form	Origin	Status in SQLite
"identifier"	ISO SQL	Conventional
`identifier`	MySQL	Accepted
[identifier]	SQL Server / MS Access	Accepted
'identifier'	None — SQLite quirk	Accepted only when nothing else matches

The fourth form is the engine’s most-cited departure from ISO SQL. A name in single quotes is primarily a string literal; if SQLite encounters a single-quoted token where it expects an identifier — typically because no column of that name exists — the token is silently interpreted as an identifier rather than rejected. The intent was to accept legacy code that mis-quoted identifiers; the effect is that misspelled column names are sometimes silently treated as string literals. The recommended remedy is to enable PRAGMA legacy_double_quotes = OFF and to consistently quote identifiers with double quotes and string literals with single quotes.

SELECT "first name" FROM customer;     -- conventional ISO form
SELECT `first name` FROM customer;     -- MySQL form
SELECT [first name] FROM customer;     -- SQL Server / Access form

String and BLOB literals

A string literal is enclosed in single quotes. A single quote inside the literal is escaped by doubling it; backslashes are not interpreted. There are no escape sequences for newline, tab, or other control characters within an SQL string literal; the convention is to embed the character itself or to construct the value with the char(n) function.

SELECT 'O''Hara';                          -- yields O'Hara
SELECT 'line one' || char(10) || 'line two';

A BLOB literal is written with the X'…' form, where the quoted contents are an even number of hexadecimal digits. Each pair of hexadecimal digits encodes one byte.

SELECT length(X'0a1b2c');     -- yields 3

Numeric literals are written in conventional decimal notation, with optional sign, optional fractional part, and optional exponent: 42, -3.14, 6.022e23. Hexadecimal integer literals (0x1A) are accepted from version 3.8.6 onwards.

Comments

SQLite recognises two comment forms, identical to those of ISO SQL.

-- A line comment runs from the two hyphens to the end of the line.
SELECT * FROM customer;

/* A block comment is delimited by slash-star and star-slash;
   block comments do not nest. */
SELECT name FROM customer WHERE id = 1;

Comments are stripped before parsing and have no effect on the meaning of a statement. The sqlite3 shell’s .dot commands are not SQL and are not commented in this way; they are line-oriented and are described in the Shell and C API page.

Diagnostic prefixes

Two prefixes affect the compilation of a SELECT, INSERT, UPDATE, or DELETE statement:

• EXPLAIN returns the virtual-machine bytecode that the SQLite VDBE will execute.
• EXPLAIN QUERY PLAN returns a tree describing the strategy: which tables are read in what order, which indexes are used, and where temporary tables or sorts are introduced.

EXPLAIN QUERY PLAN
SELECT name FROM customer WHERE id = 1;
-- 4|0|0|SEARCH customer USING INTEGER PRIMARY KEY (rowid=?)

These prefixes are diagnostic; they do not modify the database. EXPLAIN QUERY PLAN is the more useful of the two for query authors and is covered in detail under Indexes and query planning.

Parameters

Prepared statements bind values through parameters rather than through string interpolation. SQLite supports four parameter syntaxes:

Syntax	Semantics
?	Anonymous, numbered left-to-right
?N	Explicitly numbered
:name	Named
@name, $name	Named (Tcl-flavour aliases)

SELECT * FROM customer WHERE id = ? AND name = ?;
SELECT * FROM customer WHERE id = ?1 AND name = ?2;
SELECT * FROM customer WHERE id = :id AND name = :name;

Parameters are bound through the language binding (sqlite3_bind_int, cursor.execute(sql, [id, name]), the ? of Go’s database/sql, and so on). Binding parameters is the only correct way to pass user-controlled values into SQL; concatenation into the SQL text is a SQL-injection vulnerability.

What SQLite does not have

A short catalogue of features that SQLite omits, included for the benefit of programmers arriving from other database engines:

• No stored procedures, no CREATE PROCEDURE. Application logic is held in the host language; SQL-level encapsulation is achieved through views and triggers.
• No GRANT, no REVOKE, no users or roles. Access control is performed by the host application (typically through file-system permissions on the database file).
• No DESCRIBE statement; the equivalent is PRAGMA table_info(<table>).
• No autonomous transactions; nested BEGIN / COMMIT is approximated by SAVEPOINT.
• No RIGHT JOIN or FULL OUTER JOIN before version 3.39.
• No fixed-length character or numeric types (CHAR(N), DECIMAL(P, S) are accepted as type names but their length and precision are ignored under the default type-affinity rules).
• No BOOLEAN storage class; TRUE and FALSE are recognised as the integer values 1 and 0.

The omissions are deliberate. SQLite’s design principle is small, fast, reliable: features that complicate implementation or impose runtime costs without commensurate benefit have been left out, and the result is an engine that can be embedded in a hearing aid as readily as in a server.