Resources · 68

Data joins: avoid multiplying business metrics

Verify grain, keys and cardinality before totaling sales, costs or conversions.

· 7 min

Fictional empty analysis room with two tables of blank index cards, brass lamps and a burgundy dossier AI-generated editorial illustration

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

  1. 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.

  2. 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.

  3. 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.

  4. 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

  1. Define order and line-item granularity.
  2. Join an order with two lines; compare totals.
  3. Keep keys, row counts and pre/post aggregation totals.

PostgreSQL — Table expressions and joins

Reusable worksheet

Complete with your authorised observations. These fields are a working template, not observed results.

FieldInformation to record
GrainRow meaning and expected key
CardinalityOccurrences, null values and expected relationship
ReconciliationCounts, 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

MechanismPurposeCheck or limitation
INNER JOINRetain matched rowsUnmatched rows disappear
LEFT JOINRetain left-side rowsLater filters can change that effect

Management indicators

IndicatorWhat it measuresFirst action
Total differenceDifference from the independent sumBlock publication of unexplained totals
Unmatched shareKeys without a match in scopeDocument 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

Four reference points for reading a join
Reference pointDocumented SQL effectAcceptance question
Pairs and cardinalityEach true match produces a pair; LEFT null-extends left rows with no match.How many right rows match each left identifier?
ON and WHEREON decides matches before LEFT extension; WHERE then retains only rows with a true predicate.Does the filter define the relationship or final population?
NULL keysOrdinary equality with NULL yields unknown. IS NOT DISTINCT FROM treats two nulls as not distinct.Does matching two missing values make business sense?
AggregatesCOUNT(*) 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.

Left source lab_left
l.idl.keyl.metric
L1A100
L2A200
L3B50
L4C0
L5NULL80
L6DNULL
Right source lab_right
r.idr.keyr.state
R1Aactive
R2Ainactive
R3Bactive
R4NULLactive
R5Eactive
R6DNULL

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.

What the six results actually count

What the six results actually count
ExpressionReading 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
LEFT with repeated key A
Join / filter / keys / right sourceExpected reading
LEFT / none / equal / full8 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
INNER and removed records
Join / filter / keys / right sourceExpected reading
INNER / none / equal / full6 rows, 4 left identifiers, 5 measurements; sum 650. L4 and L5 disappear; A duplication remains.
J03 · Active filter in ON
Active filter in ON
Join / filter / keys / right sourceExpected reading
LEFT / on / equal / full6 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
Same filter in WHERE
Join / filter / keys / right sourceExpected reading
LEFT / where / equal / full3 rows and 3 left identifiers; sum 350. The filter removes rows without active state, including null-extended rows.
J05 · Two null keys matched
Two null keys matched
Join / filter / keys / right sourceExpected reading
LEFT / none / safe / full8 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
Misleading equal sum
Join / filter / keys / right sourceExpected reading
INNER / where / safe / full4 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
Empty result and null sum
Join / filter / keys / right sourceExpected reading
INNER / none / equal / emptyNo 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
Empty right, preserved left
Join / filter / keys / right sourceExpected reading
LEFT / none / equal / empty6 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
WHERE removes every extended row
Join / filter / keys / right sourceExpected reading
LEFT / where / equal / emptyLEFT 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

Prepare evidence for a reliable reconciliation
Suggested checkUseful record
Grain and keysDescribe one row in each source, its identifiers and expected business cardinality. Repeated keys may be legitimate.
Scope and stateFix period, time zone, source versions and relevant state date before joining.
MatchesList records with zero, one or several matches; retain identifiers from both sides.
Missing valuesSeparate null key, null measurement, null state and absent right record. Do not replace all four with zero.
AggregationCompare at the business grain before and after joining. Document any allocation rule and reconcile its result.
RegressionKeep 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.