Resources · 68
Data joins: avoid multiplying business metrics
Verify grain, keys and cardinality before totaling sales, costs or conversions.
· 7 min
What this guide helps achieve
- Describe the grain
- Check cardinality
- Reconcile totals
- Review exclusions
Quick check
- What does one row represent in each table?
- Do keys have the expected cardinality?
- Does the independent total remain unchanged?
Step-by-step method
- 01
Describe the grain
State what one row represents: order, order line, payment or event. Identify expected keys and time versions. Repeated customer keys are not necessarily faulty duplicates.
Deliverable: grain dictionary.
- 02
Check cardinality
Count key occurrences on both sides and find null values. Compare row counts before and after joining. Define whether the expected relationship is one-to-one, one-to-many or many-to-many.
Deliverable: key profile and exceptions.
- 03
Reconcile totals
Keep an independent sum at the business grain. Joining an order to multiple lines can repeat its amount. Aggregate at the appropriate grain before joining or define an explicit allocation, then reconcile the result.
Deliverable: before-and-after balance.
- 04
Review exclusions
Filtering the right table after an outer join can remove unmatched rows. Measure unmatched records, time periods and time zones. Retain test cases when adding new sources.
Deliverable: coverage report and regression cases.
Acceptance case to reproduce
Fictional example: these inputs describe no customer or observed result.
View case inputs
{
"orders": [
{
"id": 1,
"total": 100
},
{
"id": 2,
"total": 200
}
],
"line_order_ids": [
1,
1,
2
],
"sum_before": 300,
"sum_after_raw_join": 400
}Expected decision
The total 400 counts an order twice. Aggregate lines by order to recover the total 300. Also test two fictional orders of 100 each: their total is 200. SUM(DISTINCT total) would return 100 and remove a legitimate value. Check identifiers and join cardinality rather than deduplicating amounts.
Reproduce the diagnosis in SQL
Fictional example: two orders of 100 and 200, with three lines belonging to 1, 1 and 2. These queries use only VALUES; run them in a test environment.
WITH orders(id, total) AS (VALUES (1, 100), (2, 200)),
lines(order_id) AS (VALUES (1), (1), (2))
SELECT COUNT(*) AS rows, SUM(o.total) AS total
FROM orders o JOIN lines l ON l.order_id = o.id;WITH orders(id, total) AS (VALUES (1, 100), (2, 200)),
lines(order_id) AS (VALUES (1), (1), (2)),
per_order AS (
SELECT order_id, COUNT(*) AS line_count
FROM lines GROUP BY order_id
)
SELECT COUNT(*) AS orders, SUM(o.total) AS total
FROM orders o LEFT JOIN per_order l ON l.order_id = o.id;The direct join returns three rows and 400. Pre-aggregation restores two orders and 300.
WITH orders(id, total) AS (VALUES (1, 100), (2, 100))
SELECT SUM(total) AS correct_total,
SUM(DISTINCT total) AS distinct_total
FROM orders;With two amounts of 100, the correct sum is 200; SUM(DISTINCT total) returns 100.
Verification procedure
- Define order and line-item granularity.
- Join an order with two lines; compare totals.
- Keep keys, row counts and pre/post aggregation totals.
Reusable worksheet
Complete with your authorised observations. These fields are a working template, not observed results.
| Field | Information to record |
|---|---|
| Grain | Row meaning and expected key |
| Cardinality | Occurrences, null values and expected relationship |
| Reconciliation | Counts, reference sum and differences |
Worked example
Illustrative situation
Fictional example: an order of 100 is linked to two product lines.
Decision and expected evidence
Summing the order amount after joining yields 200. The team aggregates lines before reconciliation and checks the independent total of 100.
Distinguish the mechanisms
| Mechanism | Purpose | Check or limitation |
|---|---|---|
| INNER JOIN | Retain matched rows | Unmatched rows disappear |
| LEFT JOIN | Retain left-side rows | Later filters can change that effect |
Management indicators
| Indicator | What it measures | First action |
|---|---|---|
| Total difference | Difference from the independent sum | Block publication of unexplained totals |
| Unmatched share | Keys without a match in scope | Document exclusions and period |
Common pitfalls
- Sum a value repeated at another grain
- Conceal cardinality errors with DISTINCT
Frequently asked questions
Does DISTINCT fix an incorrect join?
It may conceal repeats without fixing the grain or allocation. Verify the relationship before deduplicating.
Are repeated keys always wrong?
No: order lines normally repeat an order identifier. Check against the expected grain.
What small test helps?
A fictional order of 100 with two lines should retain an order total of 100, not 200 after joining.
Join cardinality, NULL and aggregates
A join transforms a set of rows before metrics are computed. To investigate a difference, inspect the resulting pairs and removed records: comparing two sums is insufficient. This workshop follows two fictional sources through to six aggregates without connecting to a database.
Documentary reference: PostgreSQL 18, chapters 7.2, 9.2 and 9.21, reviewed on 23 October 2026. Data, scenarios and acceptance suggestions are original CIE examples. They describe no real company, person or observed performance.
PostgreSQL 18 · Table expressions · PostgreSQL 18 · documentation reviewed 2026-10-23
Four reference points for reading a join
| Reference point | Documented SQL effect | Acceptance question |
|---|---|---|
| Pairs and cardinality | Each true match produces a pair; LEFT null-extends left rows with no match. | How many right rows match each left identifier? |
| ON and WHERE | ON decides matches before LEFT extension; WHERE then retains only rows with a true predicate. | Does the filter define the relationship or final population? |
| NULL keys | Ordinary equality with NULL yields unknown. IS NOT DISTINCT FROM treats two nulls as not distinct. | Does matching two missing values make business sense? |
| Aggregates | COUNT(*) counts rows; COUNT(expression) counts non-null values; SUM ignores null values. | What is the contribution grain and what does a missing value mean? |
PostgreSQL 18 · Comparison functions · PostgreSQL 18 · documentation reviewed 2026-10-23
Two fictional six-row sources
Each left row is an independent technical record identified by l.id; metric is an integer quantity with no business unit. A right row is a state associated with a key. Key A repeats on both sides; row identifiers are unique and non-null. NULL denotes a missing value, never the text “NULL”. L4’s zero is a present measurement. Left baseline: 6 rows, 5 non-null measurements and SUM(metric) = 430; one measurement is unknown, so this is not a complete total of every quantity.
| l.id | l.key | l.metric |
|---|---|---|
| L1 | A | 100 |
| L2 | A | 200 |
| L3 | B | 50 |
| L4 | C | 0 |
| L5 | NULL | 80 |
| L6 | D | NULL |
| r.id | r.key | r.state |
|---|---|---|
| R1 | A | active |
| R2 | A | inactive |
| R3 | B | active |
| R4 | NULL | active |
| R5 | E | active |
| R6 | D | NULL |
Row reconciliation workshop
Choose INNER or LEFT, the position of r.state = 'active', key comparison and a full or empty right source. The 24 configurations are bounded to these data. The result table shows each pair and null-extended rows. Six aggregates apply to the result after any WHERE filter.
This local reader executes no SQL, collects no data and offers no import or export. It covers no collations, type conversion, composite keys, temporal joins, permissions, performance or other SQL dialects. Identifier ordering is added for readability; a join alone does not guarantee this order.
Keep the left reference of 430 over five known measurements for comparison. An unchanged sum after joining does not establish matching quality: duplication and exclusion may offset each other.
Result over final rows
| l.id | l.key | l.metric | r.id | r.key | r.state | Pair status |
|---|
Row query corresponding to the choices
lab_left, lab_right and lab_right_empty are illustrative names; lab_right_empty has the lab_right schema with zero rows. Displayed code explains the choices and is not executed. Aggregates below are computed over these rows without GROUP BY.
What the six results actually count
| Expression | Reading in this exercise |
|---|---|
| COUNT(*) | All final rows, including null-extended LEFT rows. |
| COUNT(DISTINCT l.id) | Distinct retained left identifiers. This count does not repair a repeated sum. |
| COUNT(r.id) | Rows with an actual right record: source r.id is always non-null. |
| COUNT(r.key) | Non-null right keys. May be below COUNT(r.id) when a null-key record matched. |
| COUNT(l.metric) | Occurrences of non-null left measurements, including repetitions; zero counts as present. |
| SUM(l.metric) | Sum of non-null values after joining. NULL when no non-null value contributes, including an empty result. |
PostgreSQL 18 · Aggregate functions · PostgreSQL 18 · documentation reviewed 2026-10-23
Nine explained scenarios, available without JavaScript
J01 · LEFT with repeated key A
| Join / filter / keys / right source | Expected reading |
|---|---|
| LEFT / none / equal / full | 8 rows, 6 left identifiers, 6 present r.id, 6 present r.key, 7 present measurements; sum 730. A produces four pairs. |
J02 · INNER and removed records
| Join / filter / keys / right source | Expected reading |
|---|---|
| INNER / none / equal / full | 6 rows, 4 left identifiers, 5 measurements; sum 650. L4 and L5 disappear; A duplication remains. |
J03 · Active filter in ON
| Join / filter / keys / right source | Expected reading |
|---|---|
| LEFT / on / equal / full | 6 rows and 6 left identifiers; 3 present r.id, 5 measurements; sum 430. L4, L5 and L6 retain null-extended right values. |
J04 · Same filter in WHERE
| Join / filter / keys / right source | Expected reading |
|---|---|
| LEFT / where / equal / full | 3 rows and 3 left identifiers; sum 350. The filter removes rows without active state, including null-extended rows. |
J05 · Two null keys matched
| Join / filter / keys / right source | Expected reading |
|---|---|
| LEFT / none / safe / full | 8 rows, 7 present r.id but 6 present r.key; sum 730. L5 actually matches R4; null r.key therefore does not establish an unmatched row. |
J06 · Misleading equal sum
| Join / filter / keys / right source | Expected reading |
|---|---|
| INNER / where / safe / full | 4 rows, 4 left identifiers and sum 430: same sum as the baseline, although L4 and L6 are absent. Equal totals do not mean equal populations. |
J07 · Empty result and null sum
| Join / filter / keys / right source | Expected reading |
|---|---|
| INNER / none / equal / empty | No rows. All five COUNT values are 0; SUM(l.metric) is NULL. The reader does not replace this absence with zero. |
J08 · Empty right, preserved left
| Join / filter / keys / right source | Expected reading |
|---|---|
| LEFT / none / equal / empty | 6 rows, 6 left identifiers, no present r.id or r.key; 5 measurements and sum 430. Left values remain present. |
J09 · WHERE removes every extended row
| Join / filter / keys / right source | Expected reading |
|---|---|
| LEFT / where / equal / empty | LEFT first produces six extended rows; the active WHERE predicate removes all of them. Five COUNT values 0, sum NULL. |
Prepare evidence for a reliable reconciliation
| Suggested check | Useful record |
|---|---|
| Grain and keys | Describe one row in each source, its identifiers and expected business cardinality. Repeated keys may be legitimate. |
| Scope and state | Fix period, time zone, source versions and relevant state date before joining. |
| Matches | List records with zero, one or several matches; retain identifiers from both sides. |
| Missing values | Separate null key, null measurement, null state and absent right record. Do not replace all four with zero. |
| Aggregation | Compare at the business grain before and after joining. Document any allocation rule and reconcile its result. |
| Regression | Keep repeated keys, null keys, zero, unknown measurement and empty-source cases; check rows as well as totals. |
In this exercise two left A rows and two right A rows produce four pairs without a filter. Removing a right row or applying DISTINCT without understanding its role can lose valid information. Choose prior aggregation, a more precise key or an allocation rule only after defining the business need.
Sources, edition and credits
Official PostgreSQL Global Development Group documentation, edition 18. Original CIE summary, fictional data and educational reader; no PostgreSQL engine or code is embedded. References describe the selected semantics and do not certify this reader.
PostgreSQL 18 · Table expressions · PostgreSQL 18 · documentation reviewed 2026-10-23
SHA-256 : 252f02babbaec8ac702ff2966db75d0b38886cb576a90bbfc458bc4abd0dc688
PostgreSQL 18 · Aggregate functions · PostgreSQL 18 · documentation reviewed 2026-10-23
SHA-256 : c466d05fe796e77f1362dae39c8b212c7bf381065904eb555fa91537e363e5d0
PostgreSQL 18 · Comparison functions · PostgreSQL 18 · documentation reviewed 2026-10-23
SHA-256 : f08404f5624f8b87a91f117be7d59feca075508b124552b66b61940b5b6ef28c
Official references
References consulted: . The method and worksheet propose checks to adapt to your context; they do not constitute certification.
