Why Did My LEFT JOIN Lose Unmatched Records?
Resolve a pipeline reconciliation doubt by placing filters correctly and checking unmatched rows.
The doubt: why did a LEFT JOIN behave like an inner join?
A condition on the right-hand table in WHERE can discard the NULL-extended rows created for unmatched records. Read the entire query rather than identifying its behaviour from the join keyword alone. This matters when reconciling source records against optional reference data.
Suppose every incoming event must appear in an audit report, whether or not an active reference match exists. If the query filters right-side active status after joining, an event with no reference row can disappear. The missing audit row may then be mistaken for a successful ingestion or a missing source event.
Preserve the intended input set
This PostgreSQL example was executed locally with PGlite. The placement principle also applies to GoogleSQL; platform-specific type and syntax differences still need checking.
WITH events(id) AS (VALUES (1),(2),(3)),
refs(id, active) AS (VALUES (1,true),(2,false))
SELECT e.id, r.active
FROM events e
LEFT JOIN refs r ON e.id = r.id AND r.active = true
ORDER BY e.id;Expected rows: 1/true, 2/NULL, 3/NULL. Inactive reference 2 does not satisfy the match condition, but event 2 remains. Move the active condition into WHERE instead, keeping only id equality in ON: only event 1 remains.
Is moving a filter always the correct fix?
No. If the report genuinely wants only events with active references, discarding the others can be intentional. Clarify whether the requirement is all events with optional active details or only actively matched events. Query changes should follow that meaning, not a rule that every LEFT JOIN must retain every input.
How do duplicate reference keys affect the result?
Several matching reference rows can multiply an event. Preserving unmatched events does not prevent this other failure. Validate the reference key contract and inspect match counts before using the joined output for sums or audit totals. DISTINCT can hide symptoms without selecting the correct reference record.
Distinguish absence from missing attributes
Checking r.active for NULL does not always prove that a reference record is absent; a matched row might itself have a missing attribute. Use a known non-null matched key or explicit match indicator to identify absence. Document the reference table's nullable fields and key assumptions.
Practice and expected result
Add an active reference for event 3. The output still has three events, but event 3 now shows true. Add a second active reference for event 1 and predict four output rows. Explain why that violates a one-reference-per-event expectation. A useful pipeline check compares input IDs, output IDs and match multiplicity separately instead of merely checking job success.
GCP Data Engineering course.
BigQuery query and join syntax.
Prepared by AI Leaf Global Learn for classroom learners in Mudichur/Tambaram, Chennai, and online learners across India. Confirm current course and batch details. Area tags describe audiences, not additional offices.