SQLite supports 99% of standard SQL: SELECT, INSERT, UPDATE, DELETE, JOIN (INNER, LEFT, CROSS), GROUP BY, HAVING, ORDER BY, LIMIT, subqueries, CTEs, window functions, transactions (BEGIN, COMMIT, ROLLBACK). Differences from MySQL/PostgreSQL: (1) No RIGHT JOIN, no FULL OUTER JOIN (use LEFT JOIN + UNION), (2) Limited ALTER TABLE (can't drop columns in old versions), (3) No stored procedures (implement in app code), (4) Different data types (TEXT, INTEGER, REAL, BLOB, NULL - no VARCHAR, no DATETIME), (5) Type affinity (flexible typing, "123" can be integer or text). SQLite-specific features: (1) PRAGMA commands (PRAGMA journal_mode=WAL, PRAGMA foreign_keys=ON), (2) ATTACH DATABASE (query multiple databases), (3) Full-text search (FTS5), (4) JSON functions (json_extract, json_array), (5) R-Tree indexes (spatial data).

Standard SQL: SELECT, INSERT, UPDATE, DELETE, JOIN (INNER, LEFT, CROSS), GROUP BY, HAVING, ORDER BY, LIMIT, subqueries, CTEs, window functions

No RIGHT JOIN, no FULL OUTER JOIN (workaround: LEFT JOIN + UNION or rewrite query)

Limited ALTER TABLE: Can't drop columns (before 3.35.0), can't modify column type (workaround: create new table, copy data, rename)

Type affinity: Flexible typing (TEXT, INTEGER, REAL, BLOB, NULL), "123" can be integer or text depending on context

PRAGMA commands: PRAGMA journal_mode=WAL (enable WAL), PRAGMA foreign_keys=ON (enable FK constraints), PRAGMA synchronous=NORMAL (faster writes)

Transactions: BEGIN TRANSACTION, COMMIT, ROLLBACK (same as server databases), SAVEPOINT for partial rollback

ATTACH DATABASE: Query multiple databases (ATTACH DATABASE 'other.db' AS other; SELECT * FROM other.table;)

Client-server, multi-writer concurrency, network access, user management, good for web servers

Client-server, MVCC concurrency, advanced features, good for complex queries and high concurrency

Client-server, enterprise features, Windows integration, good for Microsoft ecosystem

Client-server, enterprise features, expensive, good for large corporations

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

TIME

Stores a time-of-day value without any date component.

TIME '14:30:00'

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY

NOT NULL

Prevents missing values in a column that must always contain data.

email VARCHAR(255) NOT NULL

CHECK