8.4 Resolving Name Clashes Using Qualifiers and Aliases
When you join tables, it's often the case that the tables contain columns with the same names. If you refer to such a column in the query, it's ambiguous which table the column reference applies to. This ambiguity usually can be addressed by qualifying column names with table names. However, if you join a table to itself, even the table name is ambiguous and it's necessary to use aliases to disambiguate table references. This section describes how to address naming issues in queries by qualifying column and table names and by using aliases.
8.4.1 Qualifying Column Names
In each of the joins shown earlier in this chapter, the column names are unambiguous because no query refers to a column that appears in more than one of the joined tables. But it will often be the case that a join involves tables that have similarly named columns. If a column name used in the query appears in more than one table, the name is ambiguous and it's necessary to provide information that identifies which table you mean. To do this, qualify the column name with the appropriate table name.
Suppose that you want to list, for each country named in the Country table, all of its cities named in the City table. In principle, this is a simple query that associates country names and city names based on the country codes that are common to the two tables. In practice, there is a small complication:
mysql> SELECT Name, Name FROM Country, City
-> WHERE Code = CountryCode;
ERROR 1052: Column: 'Name' in field list is ambiguous
The problem here is that the country name column in the Country table and the city name column in the City table both are called Name. MySQL has no way to know which instance of Name in the query goes with which table.
To resolve this ambiguity, qualify the references to Name with the appropriate table name so that MySQL can tell which table to use for each reference:
mysql> SELECT Country.Name, City.Name FROM Country, City
-> WHERE Code = CountryCode;
+-------------+----------------+
| Name | Name |
+-------------+----------------+
| Afghanistan | Kabul |
| Afghanistan | Qandahar |
| Afghanistan | Herat |
| Afghanistan | Mazar-e-Sharif |
| Netherlands | Amsterdam |
| Netherlands | Rotterdam |
| Netherlands | Haag |
| Netherlands | Utrecht |
| Netherlands | Eindhoven |
| Netherlands | Tilburg |
| Netherlands | Groningen |
| Netherlands | Breda |
| Netherlands | Apeldoorn |
| Netherlands | Nijmegen |
| Netherlands | Enschede |
...
Note that although it might not always be necessary to provide table qualifiers in a join, it's always allowable to do so. Thus, although Code and CountryCode in the preceding example are unambiguous because each appears in only one table, you can qualify them explicitly if you want to do so:
mysql> SELECT Country.Name, City.Name FROM Country, City
-> WHERE Country.Code = City.CountryCode;
+-------------+----------------+
| Name | Name |
+-------------+----------------+
| Afghanistan | Kabul |
| Afghanistan | Qandahar |
| Afghanistan | Herat |
| Afghanistan | Mazar-e-Sharif |
| Netherlands | Amsterdam |
| Netherlands | Rotterdam |
| Netherlands | Haag |
| Netherlands | Utrecht |
| Netherlands | Eindhoven |
| Netherlands | Tilburg |
| Netherlands | Groningen |
| Netherlands | Breda |
| Netherlands | Apeldoorn |
| Netherlands | Nijmegen |
| Netherlands | Enschede |
...
Adding qualifiers even when they aren't necessary for MySQL to understand a query often can make the query easier for people to understand, particularly people unfamiliar with the tables. Without the qualifiers, it might not be evident which table each column comes from.
More complex queries might involve multiple ambiguous columns. For example, the Country and City tables each have a Population column, and you can compare them to identify cities that contain more than 75% of their country's population:
mysql> SELECT Country.Name, Country.Population, City.Name, City.Population
-> FROM City, Country
-> WHERE City.CountryCode = Country.Code
-> AND Country.Population * .75 < City.Population;
+---------------------------+------------+--------------+------------+
| Name | Population | Name | Population |
+---------------------------+------------+--------------+------------+
| Falkland Islands | 2000 | Stanley | 1636 |
| Gibraltar | 25000 | Gibraltar | 27025 |
| Cocos (Keeling) Islands | 600 | Bantam | 503 |
| Macao | 473000 | Macao | 437500 |
| Pitcairn | 50 | Adamstown | 42 |
| Saint Pierre and Miquelon | 7000 | Saint-Pierre | 5808 |
| Singapore | 3567000 | Singapore | 4017733 |
+---------------------------+------------+--------------+------------+
Both Name and Population require table qualifiers in this query because both are ambiguous.
8.4.2 Qualifying and Aliasing Table Names
Table qualifiers resolve many column name ambiguities, but sometimes even the table name is ambiguous. This happens in two ways.
First, you might perform a join between tables that have the same name but come from different databases. In this case, you provide not only table names as qualifiers, but database names as well. Suppose that two databases world1 and world2 both have a table named Country and that you want to determine which names are present in both tables. The query can be written like this:
SELECT world1.Country.Name
FROM world1.Country, world2.Country
WHERE world1.Country.Name = world2.Country.Name;
Second, a table name is always ambiguous when you join the table 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, you can use a self-join. However, you cannot write the query like this:
mysql> SELECT IndepYear, Name, Name
-> FROM Country, Country
-> WHERE IndepYear = IndepYear AND Name = 'Qatar';
ERROR 1066: Not unique table/alias: 'Country'
Furthermore, you cannot remove the ambiguity 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, Country
-> WHERE Country.IndepYear = Country.IndepYear
-> AND Country.Name = 'Qatar';
ERROR 1066: 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 you alternative unambiguous names 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, Country AS t2
-> WHERE 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 |
+-----------+-------+----------------------+
| 
