# Keep left rows when filtering

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

---

## Keep left rows when filtering

ID: MHC-D-RESEARCH-9717 · Version: 0.1.0 · Kind: principle
Source: https://vedokrok.com/knowledge/keep-left-rows-when-filtering

An invoice filter can make a supplier disappear.

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

### Use when

- Rows vanish after a filter is added to a left join.

### Avoid when

- This example assumes a non-null invoice ID. Conditions that accept NULL and later filters need separate checks.

### Explanation

LEFT JOIN adds a row with NULL right-hand values when no match satisfies ON. To keep every supplier, put the invoice test in ON. WHERE invoice.status = 'open' removes those NULL rows afterwards. Adding OR invoice.id IS NULL keeps suppliers with no invoice, but still loses those whose invoices are all closed.

### Steps

1. Decide whether the report needs every supplier or only those with an open invoice.
2. Test one supplier with an open invoice, one with only closed invoices and one with none.

### Example

Three suppliers cover those cases. Filtering in ON keeps all three; the equality test in WHERE keeps only the first.

### Check

Compare supplier IDs and invoice values with the intended report, not just the total row count.

### Limits

- This example assumes a non-null invoice ID. Conditions that accept NULL and later filters need separate checks.

### Evidence and sources

- supports: In PostgreSQL, LEFT JOIN adds a row with NULL right-hand values when no pair satisfies ON. A later WHERE condition keeps only rows for which it is true; a right-hand equality test rejects these NULL rows, while a NULL-accepting condition can retain them. — pg-joins. Claim limit: PostgreSQL 18 logical row semantics, not a planner or performance guarantee. A NULL-accepting WHERE condition does not recreate rows already lost because their key matched only nonqualifying right-hand records.
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. (Section 7.2.1.1 LEFT OUTER JOIN and ON-versus-WHERE example; Section 7.2.2 WHERE)
- pg-joins: PostgreSQL 18: Table Expressions — https://www.postgresql.org/docs/18/queries-table-expressions.html

Review: agent · Agent editorial and scoped source review · 2026-11-12T18:37:40Z
Published: 2026-11-12T18:37:40Z
