Self Joins on the Same Table in MySQL
I didn't believe it was possible until someone showed me, but it is possible to do joins on the same table in MySQL. This leads to some pretty handy ways to structure nested data. The best way to explain is with examples.
Say that we've created a table of family members and how they are related:
mysql> select * from people; +----+----------------------+-----------+ | id | name | partnerId | +----+----------------------+-----------+ | 1 | Mom | 2 | | 2 | Dad | 1 | | 3 | Me | NULL | | 4 | Sister | NULL | | 5 | Brother | NULL | | 6 | Maternal Grandmother | 7 | | 7 | Maternal Grandfather | 6 | | 8 | Paternal Grandfather | 9 | | 9 | Paternal Grandmother | 8 | +----+----------------------+-----------+ 9 rows in set (0.00 sec) mysql>We can then query the data and join the table back to itself using the
partnerId
and primary id
field by aliasing the table into two different aliases. This makes MySQL think it has two tables (even though they
have the same data.) The query performs much the same as it would with two tables and benefits from indexing.
mysql> SELECT p1.name AS name, p2.name AS partner -> FROM people p1 -> LEFT JOIN people p2 ON p2.partnerId = p1.id -> ; +----------------------+----------------------+ | name | partner | +----------------------+----------------------+ | Dad | Mom | | Mom | Dad | | Maternal Grandfather | Maternal Grandmother | | Maternal Grandmother | Maternal Grandfather | | Paternal Grandmother | Paternal Grandfather | | Paternal Grandfather | Paternal Grandmother | | Me | NULL | | Sister | NULL | | Brother | NULL | +----------------------+----------------------+ 9 rows in set (0.00 sec) mysql>