INNER JOIN

Most useful questions need facts from two tables at once. In the MOT tables the shared id has two names: vehicle_id in fact_mot_test and registration in dim_vehicle.

How it works

Take each row of the left table and find the rows of the right table that satisfy the ON condition. An INNER JOIN keeps a row only when a match exists on both sides. The result has one row per matching pair.

Common mistake

In the real warehouse, dim_vehicle keeps a closed row with is_current = FALSE for a vehicle whose details changed. Joining without is_current = TRUE then repeats a test once per version of the vehicle.

Worked example

SELECT d.spec.make AS make, COUNT(*) AS vehicles
FROM `learn.mot.dim_vehicle` AS d
JOIN `learn.mot.fact_vehicle_summary` AS s ON d.registration = s.vehicle_id
WHERE d.is_current = TRUE
GROUP BY make
ORDER BY vehicles DESC, make
LIMIT 3

Each dim_vehicle row is paired with the summary row that has the same identifier. A vehicle with no summary row drops out.

The result counts vehicles per make among the paired rows.

All BigQuery lessons