SQLite § queries

SELECT and joins

SELECT is the most-used SQL statement and the principal mechanism by which an application reads data from the database. Its surface is large: clauses for projection, filtering, ordering, grouping, joining, and limiting; subqueries and common table expressions; set operations (UNION, INTERSECT, EXCEPT); window functions and recursive forms documented on dedicated pages. This page covers the core: the clauses and their processing order, the WHERE predicates documented in Operators, the ORDER BY and LIMIT clauses, the join syntax, and the relationship to relational algebra.

Clause order vs processing order

A SELECT statement is written in the order:

SELECT      column-expressions
FROM        table-or-join
WHERE       row-filter
GROUP BY    grouping-columns
HAVING      group-filter
ORDER BY    ordering
LIMIT       n OFFSET m

The clauses are processed in a different order: FROM first (the source rows are gathered), then WHERE (rows are filtered), then GROUP BY (rows are aggregated), then HAVING (groups are filtered), then SELECT (the projection is computed), then ORDER BY (rows are sorted), then LIMIT (the result is truncated).

Order written	Order processed	Purpose
1. SELECT	5	Projection: which columns appear in the output.
2. FROM	1	Source: the tables (or joins, or subqueries) to read from.
3. WHERE	2	Row filter: which rows are kept.
4. GROUP BY	3	Aggregation: rows are collapsed into groups.
5. HAVING	4	Group filter: which groups are kept.
6. ORDER BY	6	Sort order of the result.
7. LIMIT	7	Truncate the result to n rows starting at offset m.

The asymmetry has practical consequences. WHERE is processed before SELECT, so a column alias declared in SELECT is not available in WHERE:

-- Wrong: alias is not available in WHERE
SELECT amount * 1.20 AS gross FROM order WHERE gross > 100;

-- Right: repeat the expression, or use a subquery
SELECT amount * 1.20 AS gross FROM order WHERE amount * 1.20 > 100;

ORDER BY is processed after SELECT and can reference column aliases:

SELECT amount * 1.20 AS gross FROM order ORDER BY gross DESC;

The same logic explains why HAVING can reference aggregate values produced by GROUP BY while WHERE cannot: HAVING runs after the aggregation, WHERE before.

Projection: SELECT and DISTINCT

The projection is the SELECT clause’s column list. The wildcard * selects every column from the source; explicit column names are preferred in production code so that schema changes do not silently change results.

SELECT * FROM customer;
SELECT id, name, email FROM customer;
SELECT id AS customer_id, name FROM customer;     -- column alias with AS
SELECT id   customer_id, name FROM customer;       -- AS may be omitted

A column expression may be a literal, a function call, or any combination of the above:

SELECT id, upper(name) AS shouted, length(email) AS email_length FROM customer;
SELECT id, name || ' (' || age || ')' AS labelled FROM customer;

DISTINCT collapses duplicate rows in the projection:

SELECT DISTINCT email FROM customer;
SELECT DISTINCT email_domain FROM customer;

DISTINCT operates on the entire projection, not on a single column: SELECT DISTINCT first_name, last_name returns each unique pair, not each unique first name. ALL is the opposite — retain duplicates — and is the implicit default.

FROM: the source

The FROM clause names the source of rows. The simplest form is a single table; the source may also be a join, a subquery, a CTE, or a virtual-table function.

SELECT * FROM customer;
SELECT * FROM customer AS c;                       -- table alias
SELECT * FROM (SELECT * FROM customer WHERE deleted_at IS NULL) AS active;
SELECT * FROM json_each('[1,2,3]');                -- table-valued function

A table alias may shorten subsequent references:

SELECT c.name, o.amount
FROM customer AS c, bouquet_order AS o
WHERE c.id = o.customer_id;

WHERE: the row filter

WHERE accepts any boolean expression; rows for which the expression is true are kept. Expressions may use the comparison operators (=, <>, <, <=, >, >=, IS, IS NOT), the named predicates (BETWEEN, IN, LIKE, GLOB, IS NULL, EXISTS), the logical operators (AND, OR, NOT), and any other expression that evaluates to a boolean. The full surface is documented under Operators and NULL semantics.

SELECT * FROM bouquet WHERE price > 20;
SELECT * FROM bouquet WHERE price BETWEEN 20 AND 30;
SELECT * FROM bouquet WHERE bouquet_name LIKE '%love%';
SELECT * FROM customer WHERE phone IS NULL;
SELECT * FROM customer WHERE id IN (SELECT cust_id FROM bouquet_order);

WHERE predicates are the principal target of indexes; well-chosen indexes turn WHERE filters into O(log n) B-tree lookups (see Indexes and query planning).

ORDER BY

ORDER BY sorts the result. Rows are unordered without an explicit sort: the engine is free to return rows in any order, including an order that varies between runs. Always specify ORDER BY when row order matters to the consumer.

SELECT * FROM customer ORDER BY name;
SELECT * FROM customer ORDER BY name ASC;            -- explicit ascending
SELECT * FROM customer ORDER BY name DESC;           -- descending
SELECT * FROM customer ORDER BY name DESC, age ASC;  -- multi-key

ASC is the default; DESC reverses. Multi-key sorts apply the second key only when the first ties. The NULLS FIRST / NULLS LAST modifiers (3.30+) override the default placement of NULL:

SELECT * FROM customer ORDER BY phone NULLS LAST;

ORDER BY may reference column aliases declared in SELECT:

SELECT name, age * 2 AS score FROM customer ORDER BY score DESC;

It may also reference columns by their 1-based position:

SELECT name, score FROM customer ORDER BY 2 DESC;     -- by the second column

The positional form is convenient for ad-hoc queries but fragile under projection changes; the recommendation in production code is to name columns explicitly.

LIMIT and OFFSET

LIMIT n truncates the result to at most n rows. OFFSET m skips the first m rows. The clauses can be combined to implement pagination:

SELECT * FROM customer ORDER BY name LIMIT 20;            -- first page
SELECT * FROM customer ORDER BY name LIMIT 20 OFFSET 20;  -- second page
SELECT * FROM customer ORDER BY name LIMIT 20, 20;        -- alias for OFFSET form

The two-argument form LIMIT m, n is an SQLite (and MySQL) extension where m is the offset and n is the limit; the order is reversed compared to the standard form. The standard LIMIT n OFFSET m is the recommended spelling.

LIMIT without ORDER BY is non-deterministic: the rows returned vary between runs. Pagination without ORDER BY is a frequent bug.

For deep pagination, keyset pagination — using a WHERE clause on the sort key rather than OFFSET — is dramatically more efficient than OFFSET:

-- Page 100 of 20: OFFSET reads 1980 rows
SELECT * FROM customer ORDER BY id LIMIT 20 OFFSET 1980;

-- Keyset: read only 20 rows
SELECT * FROM customer WHERE id > ? ORDER BY id LIMIT 20;

Joins

A join combines rows from two or more tables. The join syntax has two equivalent forms — the comma-separated FROM list with a WHERE predicate (the older form) and the explicit JOIN keyword (the modern form). The modern form is preferred for legibility and is required for LEFT, RIGHT, and FULL OUTER joins.

CROSS JOIN

A cross join (Cartesian product) returns every combination of rows from the two tables. The form is rarely useful directly but is the conceptual basis for the other join kinds.

SELECT a, b FROM A, B;                       -- implicit cross join
SELECT a, b FROM A CROSS JOIN B;             -- explicit cross join

A cross join of two tables of size n and m returns n × m rows; it is therefore important to add a WHERE clause (or to use a more restrictive join kind) for any table of meaningful size.

INNER JOIN

An inner join combines rows from two tables where the join condition holds. JOIN without a qualifier means INNER JOIN.

SELECT c.name, o.amount
FROM customer AS c
INNER JOIN bouquet_order AS o ON o.customer_id = c.id;

-- equivalent
SELECT c.name, o.amount
FROM customer AS c, bouquet_order AS o
WHERE o.customer_id = c.id;

The ON clause specifies the join condition. The USING clause is a shorthand when the joined columns have the same name in both tables:

SELECT c.name, o.amount
FROM customer AS c
INNER JOIN bouquet_order AS o USING (customer_id);

USING requires the column to be named identically in both tables and produces a single column in the projection (rather than two columns with the same name).

LEFT JOIN

A left outer join preserves every row from the left table even if no row in the right table matches. Unmatched right-hand columns are NULL.

SELECT c.name, o.amount
FROM customer AS c
LEFT JOIN bouquet_order AS o ON o.customer_id = c.id;

The query returns one row per customer, with o.amount being NULL for customers who have no orders. LEFT JOIN is the conventional join kind for include rows from one table even when no matching rows exist in another.

RIGHT and FULL OUTER JOIN

RIGHT JOIN and FULL OUTER JOIN were added in version 3.39 (2022). A right join is the mirror of a left join — every row from the right table is preserved — and is rarely needed because reordering the tables transforms it into a left join. A full outer join preserves rows from both tables, with NULL on the side that has no match.

SELECT c.name, o.amount
FROM customer AS c
FULL OUTER JOIN bouquet_order AS o ON o.customer_id = c.id;

Before 3.39, a full outer join was emulated as a LEFT JOIN UNION ALL of the unmatched right-hand rows.

NATURAL JOIN

A natural join automatically joins on every column with the same name in both tables. The form is concise but fragile: a schema change that adds an unrelated identically-named column silently changes the join condition. The recommendation is to avoid NATURAL JOIN and to specify the join condition explicitly with ON or USING.

-- discouraged
SELECT * FROM customer NATURAL JOIN bouquet_order;

-- preferred
SELECT * FROM customer JOIN bouquet_order USING (customer_id);

Self-joins

A self-join joins a table to itself, almost always with two distinct aliases:

SELECT a.name AS manager, b.name AS report
FROM employee AS a
JOIN employee AS b ON b.manager_id = a.id;

Self-joins express recursive-style relationships in a single query without recursion; the recursive CTE is the alternative when the depth is unknown (see CTEs and recursion).

Subqueries

A subquery is a SELECT embedded within another query. Subqueries appear in three positions:

Position	Returns	Example
Scalar (in `SELECT` list, `WHERE`, etc.)	One row, one column	`(SELECT MAX(price) FROM bouquet)`
Row (in `WHERE` IN-style)	One column, many rows	`IN (SELECT id FROM order WHERE …)`
Table (in `FROM`)	A virtual table	`FROM (SELECT … FROM …) AS sub`

-- Scalar subquery
SELECT name, (SELECT COUNT(*) FROM bouquet_order WHERE customer_id = customer.id) AS orders
FROM customer;

-- Row subquery
SELECT * FROM customer
WHERE id IN (SELECT customer_id FROM bouquet_order);

-- Table subquery
SELECT category, AVG(daily_orders) AS avg_per_day
FROM (
    SELECT category, date(placed_at) AS day, COUNT(*) AS daily_orders
    FROM bouquet_order JOIN bouquet USING (bouquet_id)
    GROUP BY category, day
)
GROUP BY category;

A subquery may be correlated — it references columns from the outer query — or uncorrelated. Correlated subqueries are conceptually executed once per outer row; uncorrelated ones are executed once. The query planner is sometimes able to rewrite a correlated subquery into an equivalent join, but the rewrite is not guaranteed; the conventional advice is to express the query as a join when the rewrite is possible, reserving correlated subqueries for cases where the join form would be awkward.

For composing complex queries, common table expressions (WITH clauses) provide a more readable alternative to nested subqueries:

WITH active AS (
    SELECT id FROM customer WHERE deleted_at IS NULL
)
SELECT a.id, COUNT(o.id) AS orders
FROM active AS a
LEFT JOIN bouquet_order AS o ON o.customer_id = a.id
GROUP BY a.id;

CTEs are documented in detail under CTEs and recursion.

Set operations

UNION, INTERSECT, and EXCEPT combine the rows of two queries:

SELECT email FROM customer UNION SELECT email FROM newsletter;
SELECT email FROM customer INTERSECT SELECT email FROM newsletter;
SELECT email FROM customer EXCEPT SELECT email FROM newsletter;

UNION removes duplicates; UNION ALL retains them and is faster (no deduplication step). INTERSECT returns rows present in both queries; EXCEPT returns rows in the left query but not the right. The two queries must have the same number and type of columns.

Relational algebra correspondence

The conventional teaching of SQL pairs the language with relational algebra, the formal calculus that the language implements. The correspondence:

Algebra	Symbol	SQL
Selection	σ_p(R)	`SELECT * FROM R WHERE p`
Projection	Π_a,b(R)	`SELECT a, b FROM R`
Cartesian product	R × S	`SELECT * FROM R, S`
Union	R ∪ S	`R UNION S`
Intersection	R ∩ S	`R INTERSECT S`
Difference	R − S	`R EXCEPT S`
Natural join	R ⋈ S	`R NATURAL JOIN S`
Theta join	R ⋈_θ S	`R JOIN S ON θ`
Renaming	ρ_a/b(R)	`SELECT a AS b FROM R`

A working programmer rarely writes relational-algebra expressions directly, but the correspondence is a useful mental model when reasoning about complex queries — selection narrows rows, projection narrows columns, joins multiply rows, set operations combine results — and is the basis of the query planner’s optimisation work.

Worked example

A query that exercises the full surface, listing the top customers by lifetime spend:

SELECT
    c.id,
    c.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS lifetime_value,
    MAX(o.placed_at) AS last_order
FROM customer AS c
LEFT JOIN bouquet_order AS o ON o.customer_id = c.id
WHERE c.deleted_at IS NULL
GROUP BY c.id, c.name
HAVING lifetime_value > 100
ORDER BY lifetime_value DESC, c.name ASC
LIMIT 10;

The query joins customers to their orders, restricting to non-deleted customers (WHERE), aggregating per customer (GROUP BY), filtering to the substantial customers (HAVING), and ordering by spend (ORDER BY). Each clause performs a single, well-defined task; the whole reads like a small program. SQL’s declarative form is at its most natural for this kind of report query.