SQLite / Basic: the embedded engine and correct data / Lesson 5

Joins and trustworthy grouped summaries

Combine related SQLite rows while tracking multiplicity, missing matches, aggregate denominators, and the distinction between row and group filters.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Predict one-to-many join output.
Use outer joins without losing optional parents.
Define GROUP BY, COUNT, and HAVING at the intended grain.

A join creates matching combinations

A customer with two orders appears twice when joined to order rows. This is expected one-to-many behavior, not automatically duplicate customer data. Write the output grain before adding a total. Joining an order header to several lines repeats its header amount; joining another independent detail can multiply those repetitions again. Keys, not display names, should define relationships.

An INNER JOIN retains matches, while a LEFT JOIN also preserves each unmatched left row with missing right-side values. CROSS JOIN deliberately produces all combinations and can be useful for a small test matrix, but a missing relationship predicate can create an accidental explosion. Count matches per key and compare them with the intended relationship rather than trusting a familiar column name.

Preserve the parent with no child

The first script creates two customers and two orders for the first customer. Three result rows follow: two matches and one null-extended customer. The explicit order makes the result inspectable. A right-side primary key is a reliable missing-match indicator because a legitimately nullable descriptive attribute could be absent even on a matching order.

If all customers must remain while only open orders attach, put the child qualification in ON. A WHERE predicate requiring an open right-side status rejects the unmatched row because that comparison is unknown. These clauses answer different questions even though the query still contains LEFT JOIN. EXISTS can instead ask whether any matching child exists without multiplying the outer customer.

sql Read the optional relationship row by row

CREATE TABLE customer(id INTEGER PRIMARY KEY);
CREATE TABLE purchase(id INTEGER PRIMARY KEY,customer_id INTEGER);
INSERT INTO customer VALUES(1),(2);
INSERT INTO purchase VALUES(10,1),(11,1);
SELECT c.id AS customer,p.id AS purchase FROM customer AS c
LEFT JOIN purchase AS p ON p.customer_id=c.id ORDER BY c.id,p.id;

Expected output

customer|purchase
1|10
1|11
2|NULL

Customer1 has two matched orders; customer2 has one preserved row with a NULL child key.

Define the new grain created by GROUP BY

GROUP BY produces one output row per distinct grouping-key combination. Group by a stable customer identifier rather than merging unrelated people who share a name. Select grouping expressions and meaningful aggregates. SQLite's bare-column extension can return an ungrouped value that is not a justified description of the group, so do not rely on it as a substitute for a defined grain.

COUNT(*) counts rows, COUNT(amount) counts known amounts, and SUM or AVG omit NULL inputs under their documented rules. For an empty aggregate input, some results differ from zero; all-missing groups need explicit handling. Replacing missing amounts with zero before averaging changes the denominator and meaning. Report measurement coverage when that distinction matters.

Filter source rows before completed groups

WHERE defines membership in the aggregation input, while HAVING selects completed groups. In the second script, open orders30 and40 combine to70 and qualify; a closed200 never participates. Filtering individual amounts above50 would incorrectly remove both contributing open orders. This is a population-definition issue, not an optimizer preference or a style difference.

Conditional aggregation with CASE can compute several metrics from one defined source, provided each metric's missingness and denominator are stated. Numeric representation also matters: summing integer minor units preserves exact arithmetic within range, whereas REAL totals remain approximate. Validate control totals and accepted rows before choosing how many decimal places to display.

sql Separate row eligibility and group qualification

CREATE TABLE purchase(customer INTEGER,status TEXT,amount INTEGER);
INSERT INTO purchase VALUES(1,'open',30),(1,'open',40),
 (1,'closed',200),(2,'open',25),(2,'open',NULL);
SELECT customer,COUNT(*) AS orders,COUNT(amount) AS known,SUM(amount) AS total
FROM purchase WHERE status='open' GROUP BY customer
HAVING SUM(amount)>50 ORDER BY customer;

Expected output

customer|orders|known|total
1|2|2|70

Only customer1 qualifies with two known open amounts totaling70. Closed records are excluded before grouping.

Use tiny counterexamples before large reports

Test a parent with no children, one with one child, and one with several. Add duplicate amounts and a missing optional value. SUM(DISTINCT header.amount) does not repair join fanout: two valid orders with the same amount would collapse into one numeric value. Aggregate each detail to the required grain or compute header measures independently before combining results.

Reconcile group counts and totals with the same filtered base population. Document unmatched coverage and exclusions by relevant subgroup, not just an overall match percentage. A report can be mathematically consistent yet represent the wrong people or events. A trustworthy SQLite summary explains its grain, key relationship, membership rule, aggregate definition, and treatment of unknowns together.

Common errors and fixes

A header total increased after joining detail.

Why it happens: One-to-many combinations repeated the measure.
How to fix it: Aggregate at its owning grain before adding independent details.
Prevent it next time: Track multiplicity after each join.

An optional parent disappeared.

Why it happens: WHERE rejected the null-extended child.
How to fix it: Put the qualifying-child condition in ON when preservation is intended.
Prevent it next time: Test parents without qualifying children.

A group label describes an arbitrary row.

Why it happens: An ungrouped bare column was mistaken for a group property.
How to fix it: Select justified grouping expressions or aggregates.
Prevent it next time: Write the intended output grain first.

Check your understanding

1. An order of20 has two lines and three independent payments. How many joined combinations and repeated total result?

Show worked solution

The join can produce2×3=6 rows and a repeated header sum120. The true order total remains20. Summarize details separately at order grain; a value-level DISTINCT is not a general identity repair.

2. A customer has open amounts30 and40 and closed200. Why does WHERE amount>50 answer the wrong question?