SQLite Cheat Sheet: Every Command You Need (2026)

Basic CRUD

The four fundamental operations in SQLite. Every query you write uses at least one of them.

SELECT - Read data

-- Select specific columns
SELECT first_name, last_name, email
FROM users;

-- Select all columns
SELECT * FROM products;

-- Select with alias
SELECT first_name AS name, email AS contact
FROM users;

-- Select distinct values
SELECT DISTINCT category FROM products;

INSERT INTO - Add new rows

-- Insert a single row
INSERT INTO users (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane@example.com');

-- Insert multiple rows
INSERT INTO users (first_name, last_name, email)
VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Williams', 'bob@example.com');

-- INSERT OR REPLACE: insert or update if exists
INSERT OR REPLACE INTO settings (key, value)
VALUES ('theme', 'dark');

-- INSERT OR IGNORE: skip if conflict
INSERT OR IGNORE INTO users (email, name)
VALUES ('existing@example.com', 'Duplicate');

UPDATE - Modify existing rows

-- Update specific rows
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 42;

-- Update multiple columns
UPDATE products
SET price = price * 1.10,
    updated_at = datetime('now')
WHERE category = 'electronics';

DELETE - Remove rows

-- Delete specific rows
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2025-01-01';

-- Delete all rows (use with caution)
DELETE FROM temp_logs;

Filtering & Sorting

Control which rows are returned and in what order.

WHERE, AND, OR

SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND (region = 'US' OR region = 'EU');

IN and BETWEEN

-- IN: match any value in a list
SELECT * FROM products
WHERE category IN ('electronics', 'books', 'clothing');

-- BETWEEN: inclusive range
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

LIKE - Pattern matching (case-insensitive for ASCII)

-- % matches any sequence of characters
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- _ matches exactly one character
SELECT * FROM products WHERE sku LIKE 'SKU-____';

-- Note: LIKE is case-insensitive for ASCII characters in SQLite
SELECT * FROM users WHERE name LIKE 'john'; -- matches 'John', 'JOHN', 'john'

GLOB - Case-sensitive pattern matching

-- * matches any sequence (like % in LIKE)
SELECT * FROM files WHERE name GLOB '*.txt';

-- ? matches exactly one character (like _ in LIKE)
SELECT * FROM products WHERE sku GLOB 'SKU-????';

-- Character ranges
SELECT * FROM users WHERE name GLOB '[A-M]*'; -- names starting A through M

IS NULL, ORDER BY, LIMIT/OFFSET

-- IS NULL check
SELECT * FROM users WHERE deleted_at IS NULL;

-- Sort ascending (default) and descending
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Limit results (pagination)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3, 20 per page

Aggregation

Summarize data across multiple rows into a single result.

COUNT, SUM, AVG, MIN, MAX

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders
WHERE order_date >= '2026-01-01';

GROUP BY and HAVING

-- Revenue by category
SELECT
    category,
    COUNT(*) AS num_products,
    SUM(price) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC;

-- HAVING: filter groups after aggregation
SELECT category, COUNT(*) AS num_products
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY num_products DESC;

Remember: WHERE filters rows before grouping. HAVING filters groups after aggregation.

GROUP_CONCAT

-- Concatenate values within a group
SELECT
    department,
    GROUP_CONCAT(name, ', ') AS team_members
FROM employees
GROUP BY department;

-- With DISTINCT
SELECT
    order_id,
    GROUP_CONCAT(DISTINCT product_name) AS products
FROM order_items
JOIN products ON order_items.product_id = products.id
GROUP BY order_id;

JOINs

Combine rows from two or more tables. Note: SQLite does not support RIGHT JOIN or FULL OUTER JOIN natively, but workarounds exist.

INNER JOIN - Only matching rows from both tables

SELECT o.id, o.order_date, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN - All rows from left table, matching from right

-- All customers, even those with no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

CROSS JOIN - Every combination of rows

-- Generate all size/color combinations
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

RIGHT JOIN workaround (swap table order with LEFT JOIN)

-- SQLite has no RIGHT JOIN. Swap the tables and use LEFT JOIN:
-- Instead of: SELECT ... FROM A RIGHT JOIN B ON ...
-- Use:
SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.product_name;

FULL OUTER JOIN workaround (UNION of two LEFT JOINs)

-- SQLite has no FULL OUTER JOIN. Use UNION:
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id

UNION

SELECT c.name, o.id AS order_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Quick reference: INNER = intersection, LEFT = all left + matching right (NULLs for no match), CROSS = cartesian product. For RIGHT and FULL OUTER, use the workarounds above.

Subqueries & CTEs

Break complex queries into manageable parts.

Scalar subquery

-- Use a subquery that returns a single value
SELECT name, salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Subquery with IN

-- Customers who placed orders over $500
SELECT name, email
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 500
);

EXISTS

-- Products that have been ordered at least once
SELECT p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.id
);

WITH (CTE - Common Table Expression)

-- Monthly revenue summary
WITH monthly_revenue AS (
    SELECT
        strftime('%Y-%m', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue
ORDER BY month;

Recursive CTE (SQLite 3.8.3+)

-- Generate a number series (1 to 10)
WITH RECURSIVE cnt(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM cnt WHERE x < 10
)
SELECT x FROM cnt;

-- Traverse a hierarchy (org chart)
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

Window Functions (SQLite 3.25.0+)

Perform calculations across a set of rows related to the current row, without collapsing them into a single output row.

ROW_NUMBER, RANK, DENSE_RANK

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

ROW_NUMBER gives unique sequential numbers. RANK leaves gaps after ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3).

NTILE

-- Divide employees into 4 salary quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

LAG and LEAD

-- Compare each month's revenue to the previous month
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;

PARTITION BY

-- Rank employees within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees;

SQLite-Specific Features

Features and behaviors unique to SQLite that differ from other SQL databases.

AUTOINCREMENT vs ROWID

-- Every table has an implicit ROWID (unless WITHOUT ROWID)
SELECT rowid, * FROM users;

-- INTEGER PRIMARY KEY is an alias for ROWID
CREATE TABLE users (
    id INTEGER PRIMARY KEY,  -- alias for rowid
    name TEXT
);

-- AUTOINCREMENT prevents rowid reuse (slightly slower)
CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_name TEXT
);

Type affinity (dynamic typing)

-- SQLite uses type affinity, not strict types
-- 5 storage classes: INTEGER, TEXT, REAL, BLOB, NULL

-- Check the actual type of a value
SELECT TYPEOF(42);           -- 'integer'
SELECT TYPEOF('hello');      -- 'text'
SELECT TYPEOF(3.14);         -- 'real'
SELECT TYPEOF(NULL);         -- 'null'
SELECT TYPEOF(X'0500');      -- 'blob'

-- Columns accept any type regardless of declared type
CREATE TABLE flexible (data TEXT);
INSERT INTO flexible VALUES (42);      -- stores as INTEGER
INSERT INTO flexible VALUES ('hello'); -- stores as TEXT

UPSERT with ON CONFLICT (SQLite 3.24.0+)

-- Insert or update on conflict
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

-- Insert or update multiple columns
INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON CONFLICT(product_id)
DO UPDATE SET quantity = inventory.quantity + excluded.quantity;

RETURNING clause (SQLite 3.35.0+)

-- Get back the inserted/updated/deleted rows
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, name;

UPDATE products SET price = price * 0.9
WHERE category = 'clearance'
RETURNING id, product_name, price;

DELETE FROM sessions WHERE expires_at < datetime('now')
RETURNING user_id;

PRAGMA commands

-- Inspect table structure
PRAGMA table_info(users);

-- Enable foreign key enforcement (off by default!)
PRAGMA foreign_keys = ON;

-- Set journal mode to WAL (better concurrent reads)
PRAGMA journal_mode = WAL;

-- Check database integrity
PRAGMA integrity_check;

-- Show all indexes for a table
PRAGMA index_list(users);

-- Get database page size and count
PRAGMA page_size;
PRAGMA page_count;

Dot commands (sqlite3 CLI only)

-- List all tables
.tables

-- Show schema for all tables
.schema

-- Show schema for a specific table
.schema users

-- Change output mode
.mode column
.mode csv
.mode json

-- Import CSV data
.import data.csv users

-- Export to file
.output results.txt
SELECT * FROM users;
.output stdout

-- Show current settings
.show

String Functions

Manipulate and transform text data in SQLite.

-- || operator: concatenate strings (SQLite has no CONCAT function)
SELECT first_name || ' ' || last_name AS full_name
FROM users;

-- SUBSTR: extract part of a string
SELECT SUBSTR(phone, 1, 3) AS area_code
FROM contacts;

-- TRIM, LTRIM, RTRIM: remove whitespace or characters
SELECT TRIM(name) FROM products;
SELECT LTRIM('  hello');      -- 'hello'
SELECT RTRIM(name, '.') FROM items;

-- UPPER / LOWER: change case
SELECT UPPER(email) FROM users;
SELECT LOWER(city) FROM addresses;

-- LENGTH: count characters
SELECT name, LENGTH(name) AS name_length
FROM products
WHERE LENGTH(name) > 50;

-- REPLACE: substitute text
SELECT REPLACE(url, 'http://', 'https://') FROM links;

-- INSTR: find position of substring (1-based, 0 if not found)
SELECT INSTR(email, '@') AS at_position FROM users;

-- printf: formatted output
SELECT printf('$%.2f', price) AS formatted_price
FROM products;

-- GROUP_CONCAT: aggregate strings
SELECT department,
    GROUP_CONCAT(name, ', ') AS members
FROM employees
GROUP BY department;

Date & Time

SQLite has no dedicated date type. Dates are stored as TEXT, REAL, or INTEGER and manipulated with built-in functions.

-- Current date and time
SELECT date('now');           -- '2026-03-12'
SELECT time('now');           -- '14:30:00'
SELECT datetime('now');       -- '2026-03-12 14:30:00'

-- Julian day number
SELECT julianday('now');

-- Format with strftime
SELECT strftime('%Y-%m', order_date) AS month FROM orders;
SELECT strftime('%W', '2026-03-12');  -- week number

-- Date modifiers
SELECT date('now', '+7 days');           -- one week from now
SELECT date('now', '-1 month');          -- one month ago
SELECT date('now', 'start of month');    -- first of current month
SELECT date('now', 'start of year');     -- Jan 1 of current year
SELECT datetime('now', '+2 hours', '+30 minutes');

-- Calculate age in days
SELECT julianday('now') - julianday(created_at) AS age_days
FROM users;

-- Filter by relative dates
SELECT * FROM orders
WHERE order_date >= date('now', '-30 days');

-- Extract parts of a date
SELECT
    strftime('%Y', order_date) AS year,
    strftime('%m', order_date) AS month,
    strftime('%d', order_date) AS day
FROM orders;

Table Operations

Define and modify your database structure. Note: SQLite has limited ALTER TABLE support compared to other databases.

CREATE TABLE

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    is_active INTEGER DEFAULT 1
);

-- Create table if it doesn't exist
CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY,
    message TEXT,
    level TEXT,
    timestamp TEXT DEFAULT (datetime('now'))
);

ALTER TABLE (limited in SQLite)

-- Add a column (supported)
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename a table (supported)
ALTER TABLE users RENAME TO app_users;

-- Rename a column (SQLite 3.25.0+)
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- NOT supported in SQLite:
-- ALTER TABLE DROP COLUMN (before 3.35.0)
-- ALTER TABLE ALTER COLUMN
-- ALTER TABLE ADD CONSTRAINT

-- Workaround for unsupported operations: recreate the table
BEGIN TRANSACTION;
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);
INSERT INTO users_new SELECT id, name, email FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;

DROP TABLE

DROP TABLE IF EXISTS temp_imports;

CREATE INDEX

-- Create an index for faster lookups
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (SQLite 3.8.0+)
CREATE INDEX idx_active_users
ON users(email) WHERE is_active = 1;

-- Drop an index
DROP INDEX IF EXISTS idx_users_email;

CREATE TRIGGER

-- Automatically set updated_at on UPDATE
CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = datetime('now')
    WHERE id = NEW.id;
END;

-- Log deletions
CREATE TRIGGER log_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, record_id, timestamp)
    VALUES ('DELETE', 'orders', OLD.id, datetime('now'));
END;

CREATE VIEW

CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= date('now', '-1 year')
GROUP BY c.id;

-- Query the view like a table
SELECT * FROM active_customers WHERE order_count > 5;

VACUUM

-- Rebuild the database file, reclaiming unused space
VACUUM;

-- VACUUM INTO a new file (SQLite 3.27.0+)
VACUUM INTO '/path/to/backup.db';

Pro Tips

Write faster, more efficient SQLite queries and leverage advanced features.

EXPLAIN QUERY PLAN

-- See how SQLite will execute your query
EXPLAIN QUERY PLAN
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Look for "SCAN TABLE" in the output. If you see it on a large table, add an index on the JOIN or WHERE column.

WAL mode for better performance

-- Enable Write-Ahead Logging (persistent setting)
PRAGMA journal_mode = WAL;

-- WAL allows concurrent readers with one writer
-- Much faster than the default rollback journal for most workloads

Index strategy

-- Index columns used in WHERE, JOIN, and ORDER BY
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Covering index: include columns read by the query
CREATE INDEX idx_users_email_name ON users(email, name);

-- Check which indexes exist
PRAGMA index_list(orders);

ATTACH DATABASE

-- Work with multiple database files simultaneously
ATTACH DATABASE 'archive.db' AS archive;

-- Query across databases
SELECT * FROM main.users u
JOIN archive.orders o ON u.id = o.user_id;

-- Detach when done
DETACH DATABASE archive;

In-memory databases

-- Open an in-memory database (sqlite3 CLI)
-- sqlite3 :memory:

-- Or in application code, use ':memory:' as the filename
-- Perfect for tests and temporary data processing

JSON1 extension

-- Extract a value from JSON
SELECT json_extract(data, '$.name') AS name
FROM events;

-- Iterate over a JSON array
SELECT value
FROM events, json_each(json_extract(data, '$.tags'));

-- Check if JSON is valid
SELECT json_valid('{"key": "value"}');  -- 1

-- Build JSON objects
SELECT json_object('id', id, 'name', name) FROM users;

-- Update a JSON field
UPDATE events
SET data = json_set(data, '$.status', 'processed')
WHERE id = 1;

See also: SQL Cheat Sheet for general SQL syntax, What is SQL? for beginners, or our converter tools for SQLite to MySQL and SQLite to PostgreSQL.

Try these queries instantly - AI2SQL generates SQLite-compatible SQL from plain English so you can skip the syntax and focus on the data you need.

Frequently Asked Questions

What is the difference between SQLite and other SQL databases?

SQLite is a serverless, self-contained database engine that stores the entire database in a single file. Unlike PostgreSQL or MySQL, it requires no separate server process, making it ideal for embedded applications, mobile apps, and local development. It supports most of standard SQL but lacks RIGHT JOIN, FULL OUTER JOIN, and has limited ALTER TABLE support.

Does SQLite support window functions?

Yes, SQLite has supported window functions since version 3.25.0 (released September 2018). You can use ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), and other window functions with OVER and PARTITION BY clauses, just like in PostgreSQL or SQL Server.

How do I handle UPSERT in SQLite?

SQLite supports UPSERT via the ON CONFLICT clause since version 3.24.0. Use INSERT INTO ... ON CONFLICT(column) DO UPDATE SET ... to insert a row or update it if a conflict occurs. For older versions, use INSERT OR REPLACE or INSERT OR IGNORE as alternatives.

What are PRAGMA commands in SQLite?

PRAGMA commands are SQLite-specific statements used to query or modify database settings. Common ones include PRAGMA table_info(table_name) to inspect columns, PRAGMA foreign_keys = ON to enable foreign key enforcement, and PRAGMA journal_mode = WAL to enable Write-Ahead Logging for better concurrent read performance.

Can AI generate SQLite queries for me?

Yes. Tools like AI2SQL let you describe what you need in plain English and generate correct SQLite-compatible SQL automatically. This is especially useful for complex queries involving CTEs, window functions, or JSON operations. However, understanding the fundamentals from a cheat sheet helps you verify and refine AI-generated queries.