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.
- Give each copy its own alias, such as t1 and t2, and put the alias in front of every column.
- Match the copies on a key, such as
t1.vehicle_id = t2.vehicle_id, to pair rows that belong to the same vehicle. - To pair two different rows once, add a strict ordering such as
t1.completed_timestamp < t2.completed_timestamp. With<=a row also pairs with itself, and with<>every pair appears twice. - Both copies of a partitioned table need their own filter on the partition column. For an
INNER JOINthat filter can sit inONor inWHERE, and the rows are the same.
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.