Chapter 5 ■ MySQL priMer
Listing 5-7. Simple JOIN Example
mysql> SELECT Name, Continent, Language FROM Country JOIN CountryLanguage ON Country.Code =
CountryLanguage.CountryCode LIMIT 10;
+-------------+---------------+------------+
| Name | Continent | Language |
+-------------+---------------+------------+
| Aruba | North America | Dutch |
| Aruba | North America | English |
| Aruba | North America | Papiamento |
| Aruba | North America | Spanish |
| Afghanistan | Asia | Balochi |
| Afghanistan | Asia | Dari |
| Afghanistan | Asia | Pashto |
| Afghanistan | Asia | Turkmenian |
| Afghanistan | Asia | Uzbek |
| Angola | Africa | Ambo |
+-------------+---------------+------------+
10 rows in set (0.00 sec)
Here I used a JOIN clause that takes two tables specified such that the first table is joined to the second
table using a specific column and its values (the ON specifies the match). What the database server does is
read each row from the tables and returns only those rows where the value in the columns specified a match.
Any rows in one table that are not in the other are not returned.
■Tip But you can retrieve those rows with different joins. See the online reference manual on inner and
outer joins for more details.
Notice also that I included only a few columns. In this case, I specified the country name and continent
from the Country table and the language column from the CountryLanguage table. If the column names
were not unique (the same column appears in each table), I would have to specify them by table name such
as Country.Name. In fact, it is considered good practice to always qualify the columns in this manner.
There is one interesting anomaly in this example that I feel important to point out. In fact, some would
consider it a design flaw. Notice in the JOIN clause I specified the table and column for each table. This is
normal and correct, but notice the column name does not match in both tables. While this really doesn’t
matter and creates only a bit of extra typing, some DBAs would consider this erroneous and would have a
desire to make the common column name the same in both tables.
Another use for a join is to retrieve common, archival, or lookup data. For example, suppose you had
a table that stored details about things that do not change (or rarely change) such as cities associated with
ZIP codes or names associated with identification numbers (e.g., SSN). You could store this information in a
separate table and join the data on a common column (and values) whenever you needed. In this case, that
common column can be used as a foreign key, which is another advanced concept.
Foreign keys are used to maintain data integrity (that is, if you have data in one table that relates to
another table but the relationship needs to be consistent). For example, if you wanted to make sure when
you delete the master row that all of the detail rows are also deleted, you could declare a foreign key in
the master table to a column (or columns) to the detail table. See the online reference manual for more
information about foreign keys.