SQLite functions as an embedded library rather than a conventional server, making it unsuitable for certain scenarios. However, for numerous use cases, it's precisely the right tool. As the most widely deployed database engine globally, SQLite's permissive licensing and file-based storage for structured SQL data make it the default choice for many developers.

SQLite's SQL implementasion is remarkably sophisticated. It introduced WITH statements four years before MySQL and recently added window function support just five months after MySQL. Let's examine SQLite's SQL enhancements from versions 3.22.0 to 3.26.0.

Boolean Literals and Evaluations

Previously, SQLite treated BOOLEAN as an alias for INTEGER, with TRUE represented as 1 and FALSE as 0, similar to C language conventions. Starting with version 3.23.0, SQLite introduced true and false as keywords (mapped to 1 and 0) and added IS [NOT] TRUE | FALSE predicates. While UNKNOWN isn't supported, NULL serves the same purpose in three-valued logic.

These boolean literals enhance readability in INSERT and UPDATE statements. The IS [NOT] TRUE | FALSE predicates offer behavior distinct from comparison operators:

-- Different behaviors with NULL values
WHERE status_flag <> FALSE        -- Excludes NULL rows
WHERE status_flag IS NOT FALSE    -- Includes NULL rows

The second predicate is equivalent to the more verbose:

WHERE status_flag <> FALSE OR status_flag IS NULL

Window Functions

Version 3.25.0 brought window function support to SQLite - a significant enhancement for analytical queries. While SQLite's OVER clause closely follows the SQL standard, it has one notable limitation: the RANGE clause doesn't accept numeric or interval offsets, supporting only CURRENT ROW and UNBOUNDED PRECEDING|FOLLOWING. This limitation mirrors what SQL Server and PostgreSQL had at the time.

SQLite's window function implemantation is competitive with major databases, though it lacks some advanced features like DISTINCT in aggregates, WIDTH_BUCKET, RESPECT|IGNORE NULLS, and FROM FIRST|LAST options.

FILTER Clause

Though syntactic sugar, the FILTER clause significantly improves query readability. Compare these approaches:

-- Traditional CASE expression
SELECT SUM(amount) total,
       SUM(CASE WHEN category = 'electronics' THEN amount END) electronics_total
FROM transactions;

-- Using FILTER clause
SELECT SUM(amount) total,
       SUM(amount) FILTER(WHERE category = 'electronics') electronics_total
FROM transactions;

The FILTER clause enables selective aggregation before applying the aggregate function, making it particularly valuable for EAV-to-relational transformations. SQLite supports FILTER with window functions but curiously not with GROUP BY aggregates, requiring CASE expressions in those scenarios.

UPSERT Operations

Version 3.24.0 introduced upsert capabilities through INSERT...ON CONFLICT, providing elegant handling of primary key and unique constraint violations. You can either ignore conflicts (DO NOTHING) or update existing rows (DO UPDATE).

-- Example UPSERT syntax
INSERT INTO inventory (sku, quantity, last_updated)
VALUES ('ITEM-001', 100, CURRENT_TIMESTAMP)
ON CONFLICT(sku) 
DO UPDATE SET 
    quantity = quantity + excluded.quantity,
    last_updated = excluded.last_updated;

SQLite follows PostgreSQL's syntax for this feature. However, one parsing ambiguity requires workarounds when using INSERT with SELECT:

<codeinsert conflict="" data="excluded.data;" do="" from="" into="" on="" required="" select="" separate="" set="" source="" target="" to="" true="" update="" where=""></codeinsert>

Column Renaming

SQLite also added column renaming capability, a non-standard SQL feature:

ALTER TABLE products 
RENAME COLUMN old_price TO current_price;

This extension follows the syntax used by other dataabse systems for modifying base table column names.