Principle

Keep left rows when filtering

An invoice filter can make a supplier disappear.

About 1 min read

Before you use it

Use when
  • Rows vanish after a filter is added to a left join.

Keep this limit in mind

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

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

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.

An example

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

Check your result

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

Save and practise this idea

My learning

Read. Recall. Repeat.

Save this idea and return to it on any device.

Use email
Keep exploring

More from this collection

Diagnose data joins and quality before trusting a report

Aggregate each detail table before joining two totalsDetect changes involving NULL
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.

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.

PostgreSQL 18: Table Expressions · Section 7.2.1.1 LEFT OUTER JOIN and ON-versus-WHERE example; Section 7.2.2 WHERE

All sources (1)
Edition and review

Version 0.1.0 · Published

Edition checked · 2026-11-12

Read this idea as JSON · Release details