When tables are joined, it's often the case that the tables contain columns with the same names. If such a column is referred to 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 a join is on 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.
Qualifying Column Names
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 the column is connected to. This has been accommplished by qualifying the column name with the appropriate table name throughout the examples in this chapter. If name qualification has not been used, then problems can arise such as the following simple query that associates Country names and City names based on the Country codes that are common to the two tables. This results in a small complication:
mysql> SELECT Name, Name FROM Country JOIN City
-> ON Code = CountryCode;
ERROR 1052 (23000): Column: 'Name' in field list is ambiguous
The problem here is that the name column in the Country table and the 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 JOIN City
-> ON Code = CountryCode;
+-------------+----------------+
| Name | Name |
+-------------+----------------+
| Afghanistan | Kabul |
| Afghanistan | Qandahar |
| Afghanistan | Herat |
| Afghanistan | Mazar-e-Sharif |
| Netherlands | Amsterdam |
| Netherlands | Rotterdam |
| Netherlands | Haag |
...
ambiguity usually can be addressed by qualifying column names with table names. However, if a join is on 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.
Qualifying Column Names
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 the column is connected to. This has been accommplished by qualifying the column name with the appropriate table name throughout the examples in this chapter. If name qualification has not been used, then problems can arise such as the following simple query that associates Country names and City names based on the Country codes that are common to the two tables. This results in a small complication:
mysql> SELECT Name, Name FROM Country JOIN City
-> ON Code = CountryCode;
ERROR 1052 (23000): Column: 'Name' in field list is ambiguous
The problem here is that the name column in the Country table and the 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 JOIN City
-> ON Code = CountryCode;
+-------------+----------------+
| Name | Name |
+-------------+----------------+
| Afghanistan | Kabul |
| Afghanistan | Qandahar |
| Afghanistan | Herat |
| Afghanistan | Mazar-e-Sharif |
| Netherlands | Amsterdam |
| Netherlands | Rotterdam |
| Netherlands | Haag |
...
Comments
Post a Comment