SQLite Syntax: Statements, Keywords and Quoting Rules (Tested Reference)

SQL from other databases runs in SQLite with few changes, and the changes that matter sit in a handful of places, like double-quoted strings that SQLite can read as column names. In this article I’ll walk you through SQLite’s syntax on one page, grouped by task, with tested output for each group.

How to use this page

Find the task you need, read the table, then check the tested output under it. The examples build on each other in one database: a products table and an orders table. The full grammar, with railroad diagrams for every statement, is in the official SQL As Understood By SQLite docs.

Statement basics

In the sqlite3 shell, a semicolon ends each statement. From application code, a single statement doesn’t need one. Keywords aren’t case-sensitive: select works the same as SELECT.

Task	Syntax	Example
End a statement	`;`	`SELECT 1;`
Write a string	Single quotes	`'Pen'`
Put a quote inside a string	Double it	`'it''s'`
Comment to end of line	`--`	`-- note`
Block comment	`/* ... */`	`/* note */`

sqlite> select 1 + 1 AS two; SELECT 'it''s' AS quoted; -- a comment
SELECT /* inline comment */ 'done' AS status;
two
---
2
quoted
------
it's
status
------
done

Create and change tables

CREATE TABLE defines a table, and ALTER TABLE makes one change to it per statement.

Task	Statement
Create a table	`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);`
Add a column	`ALTER TABLE products ADD COLUMN stock INTEGER NOT NULL DEFAULT 0;`
Rename a column	`ALTER TABLE products RENAME COLUMN category TO section;`
Drop a column	`ALTER TABLE products DROP COLUMN stock;`
Add an index	`CREATE INDEX idx_products_name ON products(name);`
Drop a table	`DROP TABLE IF EXISTS products;`

sqlite> CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL CHECK (price >= 0), category TEXT DEFAULT 'misc');
sqlite> ALTER TABLE products ADD COLUMN stock INTEGER NOT NULL DEFAULT 0;
sqlite> ALTER TABLE products RENAME COLUMN category TO section;
sqlite> CREATE INDEX idx_products_name ON products(name);
sqlite> SELECT name FROM pragma_table_info('products');
name
-------
id
name
price
section
stock

Guides: CREATE TABLE, data types, IF EXISTS.

Insert, update and delete rows

UPDATE and DELETE change every row unless a WHERE clause picks the rows.

Task	Statement
Insert rows	`INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Mug', 8.0);`
Change rows	`UPDATE products SET price = 1.75 WHERE name = 'Pen';`
Delete rows	`DELETE FROM products WHERE stock < 5;`
Insert or replace by key	`INSERT OR REPLACE INTO products (id, name) VALUES (1, 'Pen');`
Get the changed rows back	add `RETURNING id, name`

sqlite> INSERT INTO products (name, price, section, stock) VALUES ('Pen', 1.5, 'office', 100), ('Mug', 8.0, 'kitchen', 12), ('Lamp', 24.0, 'home', 3);
sqlite> UPDATE products SET price = 1.75 WHERE name = 'Pen';
sqlite> DELETE FROM products WHERE stock < 5;
sqlite> SELECT * FROM products;
id  name  price  section  stock
--  ----  -----  -------  -----
1   Pen   1.75   office   100
2   Mug   8.0    kitchen  12

Guides: INSERT, UPDATE, DELETE.

Query rows

The clauses of a SELECT go in this order:

Order	Clause	Purpose
1	`SELECT columns`	What to return
2	`FROM table [JOIN other ON ...]`	Where the rows come from
3	`WHERE condition`	Filter rows
4	`GROUP BY columns`	Group rows for aggregates
5	`HAVING condition`	Filter groups
6	`ORDER BY columns [DESC]`	Sort
7	`LIMIT n OFFSET m`	Page through results

sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER REFERENCES products(id), qty INTEGER);
sqlite> INSERT INTO orders (product_id, qty) VALUES (1, 3), (1, 2), (2, 1);
sqlite> SELECT p.name, sum(o.qty) AS sold FROM products AS p JOIN orders AS o ON o.product_id = p.id GROUP BY p.name HAVING sold > 1 ORDER BY sold DESC LIMIT 5 OFFSET 0;
name  sold
----  ----
Pen   5

Guide: SELECT.

Operators and expressions

Operators work anywhere an expression is allowed, in the SELECT list or in a WHERE clause.

Operator	Meaning	Example	Result
`\|\|`	Join strings	`'Hello, ' \|\| 'world'`	`Hello, world`
`/`	Divide (integer if both sides are integers)	`7 / 2`	`3`
`IS NULL`	Test for NULL	`NULL IS NULL`	`1`
`BETWEEN`	In a range, inclusive	`5 BETWEEN 1 AND 10`	`1`
`IN`	In a list	`'b' IN ('a', 'b')`	`1`
`CASE`	If/else inside a query	`CASE WHEN 3 > 2 THEN 'yes' ELSE 'no' END`	`yes`

sqlite> SELECT 'Hello, ' || 'world' AS concat, 7 / 2 AS int_div, 7 / 2.0 AS real_div, NULL IS NULL AS is_null, 5 BETWEEN 1 AND 10 AS between_, 'b' IN ('a', 'b') AS in_list, CASE WHEN 3 > 2 THEN 'yes' ELSE 'no' END AS case_;
concat        int_div  real_div  is_null  between_  in_list  case_
------------  -------  --------  -------  --------  -------  -----
Hello, world  3        3.5       1        1         1        yes

Guides: operators, AND, OR.

Keywords and quoting names

SQLite’s keyword list has 147 entries, such as ORDER and GROUP (SQLite keywords). To use a keyword as a name, quote it:

Quoting	Means	Standard SQL?
`'word'`	A string literal	Yes
`"word"`	An identifier (a table or column name)	Yes
`[word]`	An identifier	No, accepted for SQL Server and Access compatibility
` word `	An identifier	No, accepted for MySQL compatibility

sqlite> CREATE TABLE "order" ("group" TEXT, [select] TEXT, `from` TEXT);
sqlite> INSERT INTO "order" VALUES ('a', 'b', 'c'); SELECT "group", [select], `from` FROM "order";
group  select  from
-----  ------  ----
a      b       c
sqlite> CREATE TABLE order (id INTEGER);
Error: in prepare, near "order": syntax error
  CREATE TABLE order (id INTEGER);
               ^--- error here

The docs advise quoting “any identifier that is an English language word, even if you do not have to”, because SQLite adds keywords over time.

Parameters in application code

Placeholders keep values out of the SQL text. SQLite accepts these forms (SQLite parameters):

Placeholder	Kind
`?`	Positional
`?NNN`	Numbered
`:name`, `@name`, `$name`	Named

In Python:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (name TEXT, age INTEGER)")
con.execute("INSERT INTO users VALUES (?, ?)", ("Ada", 36))                  # positional
con.execute("INSERT INTO users VALUES (:name, :age)", {"name": "Linus", "age": 28})  # named
print(con.execute("SELECT name FROM users WHERE age > ?", (30,)).fetchall())

$ python3 params.py
[('Ada',)]

SQLite-only syntax

These forms are specific to SQLite, so they won’t carry over to other databases unchanged.

Syntax	What it does	Guide
`PRAGMA name;`	Read or change an SQLite setting	PRAGMA
`INSERT OR REPLACE`, `INSERT OR IGNORE`	Conflict handling on insert	upsert
`CREATE TABLE ... STRICT`	Enforce column types	data types
`ATTACH DATABASE 'file' AS name`	Query two database files together	ATTACH
`.tables`, `.schema`	Shell dot-commands, not SQL	commands

sqlite> INSERT OR REPLACE INTO products (id, name, price, section, stock) VALUES (1, 'Pen', 2.0, 'office', 90) RETURNING id, name, price;
id  name  price
--  ----  -----
1   Pen   2.0
sqlite> PRAGMA table_info(orders);
cid  name        type     notnull  dflt_value  pk
---  ----------  -------  -------  ----------  --
0    id          INTEGER  0                    1
1    product_id  INTEGER  0                    0
2    qty         INTEGER  0                    0
sqlite> CREATE TABLE strict_t (n INTEGER) STRICT; INSERT INTO strict_t VALUES ('ten');
Error: stepping, cannot store TEXT value in INTEGER column strict_t.n (19)

Common mistakes

Double quotes around a string. "word" is a name. If no column has that name, SQLite falls back to a string, so WHERE section = "office" works. If a column does have that name, WHERE name = "name" compares the column with itself and matches every row. SQLite’s own docs call this a “misfeature” (Quirks of SQLite). Use single quotes for strings.

sqlite> SELECT name FROM products WHERE name = "Pen";
name
----
Pen
sqlite> SELECT name FROM products WHERE section = "office";
name
----
Pen
sqlite> SELECT name FROM products WHERE name = "name";
name
----
Mug
Pen

An unquoted keyword as a name. CREATE TABLE order fails. Quote it, as shown above.

MySQL-only statements. SHOW TABLES doesn’t exist. Query sqlite_schema or use .tables (show tables).

sqlite> SHOW TABLES;
Error: in prepare, near "SHOW": syntax error
  SHOW TABLES;
  ^--- error here

Integer division. 7 / 2 is 3. Write 7 / 2.0 for 3.5.
Expecting types to be enforced. Plain tables accept text in an INTEGER column. Use STRICT tables to reject it.