In some situations, it may be more convenient to be able to refer to MySQL columns and tables using different names. Let's take the example of a database used by an airline's online booking system; this example actually came up in the SitePoint Forums. The database structure can be found in airline.sql in the code archive if you want to follow along.
To represent the flights offered by the airline, the database contains two tables: Flights and Cities. Each entry in the Flights table represents an actual flight between two cities—the origin and destination of the flight. Obviously, Origin and Destination are columns in the Flights table, with other columns for things like the date and time of the flight, the type of aircraft, the flight numbers, and the various fares.
The Cities table contains a list of all the cities to which the airline flies. Thus, both the Origin and Destination columns in the Flights table will just contain IDs referring to entries in the Cities table. Now, consider these queries. To retrieve a list of flights with their origins:
mysql>SELECT Flights.Number, Cities.Name
->FROM Flights, Cities
->WHERE Flights.Origin = Cities.ID;
+--------+-----------+
| Number | Name |
+--------+-----------+
| CP110 | Montreal |
| CP226 | Sydney |
| QF2026 | Melbourne |
... ...
To obtain a list of flights with their destinations:
mysql>SELECT Flights.Number, Cities.Name
->FROM Flights, Cities
->WHERE Flights.Destination = Cities.ID;
+--------+----------+
| Number | Name |
+--------+----------+
| CP110 | Sydney |
| CP226 | Montreal |
| QF2026 | Sydney |
... ...
Now, what if we wanted to list both the origin and destination of each flight with a single query? That's pretty reasonable, right? Here's a query you might try:
mysql>SELECT Flights.Number, Cities.Name, Cities.Name
->FROM Flights, Cities
->WHERE Flights.Origin = Cities.ID
->AND Flights.Destination = Cities.ID;
Empty set (0.01 sec)
Why doesn't this work? Have another look at the query, and this time focus on what it actually says, rather than what you expect it to do. It tells MySQL to join the Flights and Cities tables and list the flight number, city name, and city name (yes, twice!) of all entries obtained, by matching up the Origin with the city ID and the Destination with the city ID. In other words, the Origin, Destination, and city ID must all be equal! This results in a list of all flights where the origin and the destination are the same! Unless your airline offers scenic flights, there aren't likely to be any entries that match this description (thus the "Empty set" result above).
What we need is a way to be able to return two different entries from the Cities table, one for the origin and one for the destination, for each result. If we had two copies of the table, one called Origins and one called Destinations, this would be much easier to do, but why maintain two tables that contain the same list of cities? The solution is to give the Cities table two different temporary names (aliases) for the purposes of this query.
If we follow the name of a table with AS Alias in the FROM portion of the SELECT query, we can give it a temporary name with which we can refer to it elsewhere in the query. Here's that first query again (to display flight numbers and origins only), but this time we've given the Cities table an alias: Origins.
mysql>SELECT Flights.Number, Origins.Name ->FROM Flights, Cities AS Origins ->WHERE Flights.Origin = Origins.ID;
This doesn't actually change the way the query works—in fact, it doesn't change the results at all—but for long table names, it can save some typing. Consider, for example, if we had given aliases of F and O to Flights and Cities, respectively. The query would be much shorter as a result.
Let's now return to our problem query. If we refer to the Cities table twice, using two different aliases, we can use a three-table join (where two of the tables are actually one and the same) to get the effect we want:
mysql>SELECT Flights.Number, Origins.Name,
-> Destinations.Name
->FROM Flights, Cities AS Origins,
-> Cities AS Destinations
->WHERE Flights.Origin = Origins.ID
->AND Flights.Destination = Destinations.ID;
+--------+-----------+----------+
| Number | Name | Name |
+--------+-----------+----------+
| CP110 | Montreal | Sydney |
| CP226 | Sydney | Montreal |
| QF2026 | Melbourne | Sydney |
... ... ...
You can also define aliases for column names. We could use this, for example, to differentiate the two Name columns in our result table above:
mysql>SELECT F.Number, O.Name AS Origin,
-> D.Name AS Destination
->FROM Flights AS F, Cities AS O, Cities AS D
->WHERE F.Origin = O.ID AND F.Destination = D.ID;
+--------+-----------+-------------+
| Number | Origin | Destination |
+--------+-----------+-------------+
| CP110 | Montreal | Sydney |
| CP226 | Sydney | Montreal |
| QF2026 | Melbourne | Sydney |
... ... ...