A transit authority ridership desk must attach contractor licenses to vehicle inspections and keep every inspection row even when a license is missing. Which join behavior should they use?
Select an answer to reveal the explanation.
Short Explanation
Inspections are the master list—you do not want to lose a bus check just because a license record is late. A left join from inspections to licenses keeps every inspection and puts nulls where licenses are missing. Inner joins quietly delete unmatched rows; cross joins explode the table.
Full Explanation
When inspections are the driving civic entity and contractor licenses are enrichment, a left join from inspections to licenses keeps every inspection row and null-fills license attributes when no match exists. That behavior matches transit maintenance reality: a completed vehicle inspection remains an operational fact even if the license feed is late, incomplete, or keyed differently. Downstream quality reports can then quantify unmatched license rates without quietly deleting safety checks from the master list.
An inner join drops inspections that lack a matching license, which erases exactly the rows a compliance desk often needs to escalate. A cross join multiplies every inspection by every license and explodes cardinality into a meaningless Cartesian product that will overwhelm GPU memory and invent false contractor links. A right-only anti-join oriented around licenses keeps license rows and discards inspections, inverting the stated requirement to preserve the inspection master.
Exam caveat: left versus outer terminology and which table sits on the left matter; putting licenses on the left would keep licenses and drop unmatched inspections—the opposite of the ridership desk’s intent. Operational check: build a tiny fixture with matched and unmatched inspection keys, run the left join on GPU, confirm all inspection_ids survive, confirm nulls only on the unmatched license columns, and log the unmatched rate as a data-quality KPI before production ETL.