MySQL 内链接

You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. For example, to find breeding pairs among your pets, you can join the pettable with itself to produce candidate pairs of live males and females of like species:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p1.death IS NULL AND p2.sex = 'm' AND p2.death IS NULL;

mysql> SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age,
                        remark FROM pet
                          INNER JOIN event
                          ON pet.name = event.name
                          WHERE event.type = 'litter';