Heuristic

Predict rows from the join condition

A matching key does not promise a matching row.

About 1 min read

Before you use it

Use when
  • A join returns more or fewer rows than you expected.

Keep this limit in mind

  • Count using the actual key expressions and comparison rules. Outer joins or null-safe equality need a different calculation.

An idea to explore.

Try this idea.

Inspect the example, try the idea in your situation, then check your next move.

Keep reading

Why it matters

For an inner join using only = on the full key, multiply the left and right counts for each shared key and add the products. Exclude NULL keys: = does not match them. Extra conditions can reduce this count; check them before treating fewer rows as an error.

Steps

  1. Write the complete ON condition and any later filters.
  2. Compute per-key products for a plain inner equality join with non-null keys.
  3. Apply remaining conditions before comparing with the final output.

An example

Two left rows and two right rows give four pairs. An extra value condition may leave only one.

Check your result

The count follows the actual matching rules and the intended row meaning.

Save and practise this idea

My learning

Read. Recall. Repeat.

Save this idea and return to it on any device.

Use email
Keep exploring

Connected ideas

You are readingPredict rows from the join condition

More from this collection

Diagnose data joins and quality before trusting a report

Keep left rows when filtering
Evidence and sources
Supports

In PostgreSQL, a qualifying join produces an output row for each matching row pair; joining two independent one-to-many detail tables through the same parent can multiply rows before aggregation.

Claim limit: This describes row multiplicity, not a universal query planner rule; choose aggregation only when the report needs one total per parent, and preserve row pairings when they are meaningful. Source reading history: selected_passages; 2026-10-11; Sections 7.2.1.1 Joined Tables; 7.2.1.3 Subqueries; 7.2.2 WHERE; 7.2.3 GROUP BY. Version 18 passages rechecked on 2026-10-11: matching row pairs, full ON conditions, derived tables, filtering and grouping. Grounds SQL semantics, not every engine's behavior or the effectiveness of the drafted reporting workflow. Agent release review: PostgreSQL 18 table expressions, NULL comparisons and aggregates, the UK Government Data Quality Framework and Microsoft Power Query profiling documentation were checked at the cited sections. The original examples and scope limits remain attached.

PostgreSQL 18: Table Expressions · Sections 7.2.1.1 Joined Tables and 7.2.1.3 Subqueries

Supports

In PostgreSQL, ordinary comparisons involving NULL yield unknown; IS [NOT] DISTINCT FROM treats NULL as a comparable value.

Claim limit: Scalar comparisons in PostgreSQL 18. Composite keys and other engines require their own matching rules; this is not a query-planner guarantee. Source reading history: selected_passages; 2026-10-11; Section 9.2 Comparison Functions and Operators. Version 18 comparison passages rechecked on 2026-10-11: ordinary comparisons involving NULL and IS [NOT] DISTINCT FROM. Source reading is not production testing or proof of the drafted workflow. Agent release review: PostgreSQL 18 table expressions, NULL comparisons and aggregates, the UK Government Data Quality Framework and Microsoft Power Query profiling documentation were checked at the cited sections. The original examples and scope limits remain attached.

PostgreSQL 18: Comparison Functions and Operators · Section 9.2 Comparison Functions and Operators

All sources (2)
Edition and review

Version 0.1.0 · Published

Edition checked · 2026-11-12

Read this idea as JSON · Release details