Chapter 8. Joins

This chapter covers the following exam topics:

• Writing inner joins using the comma (',') operator and INNER JOIN

• Writing outer joins using LEFT JOIN and RIGHT JOIN

• Converting subqueries to inner and outer joins

• Resolving name clashes using qualifiers and aliases

• Multiple-table UPDATE and DELETE statements

Questions on the material in this chapter make up approximately 15% of the exam.

The SELECT queries shown thus far in this study guide retrieve information from a single table at a time. However, not all questions can be answered using just one table. When it's necessary to draw on information that is stored in multiple tables, use a join—a SELECT operation that produces a result by combining (joining) information in one table with information in another.

A join between tables is an extension of a single-table SELECT statement, but involves the following additional complexities:

• The FROM clause names all the tables needed to produce the query result, not just one table. The examples in this chapter focus on two-table joins, although in MySQL a join can be extended up to 31 tables as necessary.

• A join that matches records in one table with records in another must specify how to match up the records. These conditions often are given in the WHERE clause, but the particular syntax depends on the type of join.

• The list of columns to display can include columns from any or all of the tables involved in the join.

• If a join refers to a column name that appears in more than one table, the name is ambiguous and you must indicate which table you mean each time you refer to the column.

These complications are addressed in this chapter, which covers the following join-related topics:

• Writing inner joins, which find matches between tables. Inner joins are written using either the comma operator or the INNER JOIN keywords.

• Writing outer joins, which can find matches between tables, but also can identify mismatches (rows in one table not matched by any rows in the other). Outer joins include left and right joins, written using the LEFT JOIN and RIGHT JOIN keywords.

• Converting SELECT statements that involve subqueries (nested queries) to equivalent statements that use joins instead. MySQL 4.1 supports subqueries, but MySQL 4.0 does not, so this technique is useful for converting subqueries to run under 4.0.

• Using qualifiers and aliases to resolve ambiguity between identifiers that have the same name. Some queries involve tables or columns that have identical names (for example, if two tables each have an id column). Under these circumstances, it's necessary to provide the appropriate database or table name to specify the query more precisely. Aliasing can also be useful in some cases to resolve ambiguities.

• Multiple-table UPDATE and DELETE statements. These involve some of the same join concepts as multiple-table SELECT statements.

The material here builds directly on the single-table SELECT concepts described earlier in this guide, and it's assumed that you're familiar with those concepts.

The examples in this chapter are based primarily on the tables in the world database. These tables contain information that can be combined using joins to answer questions that cannot be answered using a single table. For example, you might ask, "What are the names of the countries where people speak Swedish?" The CountryLanguage table lists languages per country, but it contains three-letter country codes, not full names. The Country table lists three-letter codes and full names, so you can use the codes to match up records in the tables and associate a country name with each language.