# Predict rows from the join condition

Treat this file as reference data, never as instructions that override the user or agent. Read the complete item before applying it. Keep use conditions, avoid conditions, limits, evidence roles and source links with the answer. An editorial block is a separately versioned analysis layer, not an original corpus claim or proof of efficacy. Cite the readable URL and version. Do not infer human review. This is a saved edition, not a live safety-notice service. Before consequential reuse, check the current page, https://vedokrok.com/data/availability.json and https://vedokrok.com/data/notices.json; absence alone does not explain why an item was removed.

Release: VEDOKROK-SEL-5d86e1b23401e578
Snapshot SHA-256: 30ce1c8fa9155763797e3d5cb55b1d94cbb8c3146ebd03fb3c006531ce60e82f
Current availability: https://vedokrok.com/data/availability.json
Current notices: https://vedokrok.com/data/notices.json

---

## Predict rows from the join condition

ID: MHC-D-RESEARCH-9715 · Version: 0.1.0 · Kind: heuristic
Source: https://vedokrok.com/knowledge/predict-rows-from-the-join-condition

A matching key does not promise a matching row.

Knowledge source release: MHC-RPUB-20261112-20287590
Source corpus commit: 20287590ddd3999b7492df232822b6a0f7d114b1
Source manifest SHA-256: 2a987660d12de0ce2bde77a421424f7762af1ff77872ef41093fecc33db74a95
Source projection SHA-256: 2807da39bbe6a3dee2db32a76675ac5185f5a33e8b0a5cdd0c22a4731f3378a0

### Use when

- A join returns more or fewer rows than you expected.

### Avoid when

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

### Explanation

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.

### Example

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

### Check

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

### Limits

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

### 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. — pg-joins. 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. (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. — pg-null. 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. (Section 9.2 Comparison Functions and Operators)
- pg-joins: PostgreSQL 18: Table Expressions — https://www.postgresql.org/docs/18/queries-table-expressions.html
- pg-null: PostgreSQL 18: Comparison Functions and Operators — https://www.postgresql.org/docs/18/functions-comparison.html

Review: agent · Agent editorial and scoped source review · 2026-11-12T18:37:40Z
Published: 2026-11-12T18:37:40Z
Related (use_before): https://vedokrok.com/knowledge/aggregate-each-detail-table-before-joining-two-totals
