Self Joins

A table name is always ambiguous when table is joined to itself using a self-join. For example, the Country
table in the world database contains an IndepYear column indicating the year in which each country
achieved independence. To find all countries that have the same year of independence as some given
country, a self-join can be used. However, the query cannot be written like this:

mysql> SELECT IndepYear, Name, Name
-> FROM Country JOIN Country
-> ON IndepYear = IndepYear AND Name = 'Qatar';

ERROR 1066 (42000): Not unique table/alias: 'Country'

Furthermore, the ambiguity cannot be removed from column references by preceding them with table name
qualifiers because the names remain identical:

mysql> SELECT Country.IndepYear, Country.Name, Country.Name
-> FROM Country JOIN Country
-> ON Country.IndepYear = Country.IndepYear
-> AND Country.Name = 'Qatar';

ERROR 1066 (42000): Not unique table/alias: 'Country'

It doesn't even help to add a database name qualifier because the database is the same for both tables. To
address this naming issue, create an alias for one or both table references and refer to the aliases elsewhere in the query. The aliases give an alternative unambiguous name by which to refer to each instance of the table in the query. Here is one solution that aliases both tables:

mysql> SELECT t1.IndepYear, t1.Name, t2.Name
-> FROM Country AS t1 JOIN Country AS t2
-> ON t1.IndepYear = t2.IndepYear AND t1.Name = 'Qatar';


+-----------+-------+----------------------+
| IndepYear | Name | Name |
+-----------+-------+----------------------+
| 1971 | Qatar | United Arab Emirates |
| 1971 | Qatar | Bahrain |
| 1971 | Qatar | Bangladesh |
| 1971 | Qatar | Qatar |
+-----------+-------+----------------------+