| 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 an 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 5 a join can be extended up to 61 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. 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. Writing self-joins that join a table to itself. Multiple-table UPDATE and DELETE statements. These involve some of the same join concepts as multiple-table SELECT statements.
The material in this chapter builds directly on the single-table SELECT concepts described earlier in this study guide, and it's assumed that you're familiar with those concepts. See Chapter 9, "Querying for Data." 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. |
