Sql - Self-joins
Code Life
By Pierre Marsaa
A table joined to itself
"Which trainers come from the same hometown?" compares trainers with other trainers. Nothing stops you from joining a table to itself — you just need two aliases to tell the two copies apart:
Loading data...
↗️
Loading editor...
↶
↷
t1 and t2 are the same table read twice. The condition t1.trainer_id < t2.trainer_id does two jobs: it removes the pairs of a trainer with themselves, and it keeps each pair only once (Morgan–Jordan, but not also Jordan–Morgan).
Try it without the second condition
With only t1.hometown = t2.hometown, every trainer matches themselves (4 useless rows) and Morgan–Jordan appears twice. < is the standard trick to get clean pairs.
Verify