Self-join

Sometimes the rows you want to compare live in the same table, such as two tests of one vehicle. A self-join puts them side by side.

How it works

A self-join names one table twice, under two aliases. Each alias acts like its own copy, so a row from one copy can be paired with a row from the other. The ON condition decides which pairs are kept.

Common mistake

Without a strict ordering, each pair shows up once in each direction and every row also pairs with itself. Counts inflate quietly, so check the row count on a case you can count by hand.

Worked example

SELECT t1.mot_test_id AS first_test, t2.mot_test_id AS second_test
FROM `learn.mot.fact_mot_test` AS t1
JOIN `learn.mot.fact_mot_test` AS t2
  ON t1.vehicle_id = t2.vehicle_id
  AND t1.completed_timestamp < t2.completed_timestamp
  AND t1.completed_date BETWEEN '2024-08-01' AND '2024-08-31'
  AND t2.completed_date BETWEEN '2024-08-01' AND '2024-08-31'
WHERE t1.vehicle_id = 'VEH000007'

The two aliases read fact_mot_test twice. The join key keeps each pair inside one vehicle.

The strict < keeps only the pair where the first test came before the second.

All BigQuery lessons