12.3. Writing Outer Joins

As described in the preceding sections, an inner join produces results by selecting combinations of matching rows from the joined tables. However, it cannot find non-matches; that is, instances where a row in one table has no match in another table. For example, an inner join can associate country names listed in the Country table with the languages spoken in those countries through a join based on country codes with the CountryLanguage table. But it cannot tell you which countries aren't associated with any language in the CountryLanguage table. Answering the latter question is a matter of identifying which country codes present in the Country table are not present in the CountryLanguage table.

To write a join that provides information about mismatches or missing records, use an outer join. An outer join finds matches (just like an inner join), but also identifies mismatches. Furthermore, with an appropriate WHERE clause, an outer join can filter out matches to display only the mismatches.

Two common forms of outer joins are left joins and right joins. These are written using the LEFT JOIN or RIGHT JOIN keywords rather than the comma operator or the INNER JOIN keywords.

Left and right joins can answer the same kinds of questions and differ only slightly in their syntax. That is, a left join can always be rewritten into an equivalent right join.

In the following sections, the terms "left table" and "right table" refer to the tables named first and second in the FROM clause, respectively.

12.3.1. Writing LEFT JOIN Queries

A left join is a type of outer join, written using the LEFT JOIN keywords. A left join treats the left table (the first one named) as a reference table and produces output for each row selected from it, whether or not the row is matched by rows in the right table. Like a join written with the INNER JOIN keywords, a LEFT JOIN is written using either ON or USING() after the table names in the FROM clause. The examples here use the ON syntax. See Section 12.2.2, "Writing Inner Joins with INNER JOIN," for details on USING() syntax.

To see the difference between an inner join and a left join, begin with the former. An inner join between the CountryLanguage and Country tables might be written like this:

 mysql> SELECT Name, Language     -> FROM Country INNER JOIN CountryLanguage ON Code = CountryCode; +---------------------------------------+---------------------------+ | Name                                  | Language                  | +---------------------------------------+---------------------------+ | Afghanistan                           | Balochi                   | | Afghanistan                           | Dari                      | | Afghanistan                           | Pashto                    | | Afghanistan                           | Turkmenian                | | Afghanistan                           | Uzbek                     | | Netherlands                           | Arabic                    | | Netherlands                           | Dutch                     | | Netherlands                           | Fries                     | | Netherlands                           | Turkish                   | ... | Palestine                             | Arabic                    | | Palestine                             | Hebrew                    | | United States Minor Outlying Islands  | English                   | +---------------------------------------+---------------------------+ 

That query displays information from table row combinations that have matching country code values. A LEFT JOIN has a similar syntax (replace INNER JOIN with LEFT JOIN), but produces a different result:

 mysql> SELECT Name, Language     -> FROM Country LEFT JOIN CountryLanguage ON Code = CountryCode; +----------------------------------------------+---------------------------+ | Name                                         | Language                  | +----------------------------------------------+---------------------------+ | Afghanistan                                  | Balochi                   | | Afghanistan                                  | Dari                      | | Afghanistan                                  | Pashto                    | | Afghanistan                                  | Turkmenian                | | Afghanistan                                  | Uzbek                     | | Netherlands                                  | Arabic                    | | Netherlands                                  | Dutch                     | | Netherlands                                  | Fries                     | | Netherlands                                  | Turkish                   | ... | Palestine                                    | Arabic                    | | Palestine                                    | Hebrew                    | | Antarctica                                   | NULL                      | | Bouvet Island                                | NULL                      | | British Indian Ocean Territory               | NULL                      | | South Georgia and the South Sandwich Islands | NULL                      | | Heard Island and McDonald Islands            | NULL                      | | French Southern territories                  | NULL                      | | United States Minor Outlying Islands         | English                   | +----------------------------------------------+---------------------------+ 

In this query, the left table is the one named first (Country) and the right table is the one named second (CountryLanguage).

Notice that the LEFT JOIN finds both matches and non-matches. That is, it displays all the rows produced by the inner join, plus a few more besides:

• If a row from the left table matches any right table rows, the result includes for each match a row containing the left table columns and the right table columns. These are rows that an inner join also will produce.

• If the left table row doesn't match any right table rows, the result includes a row containing the left table column values and NULL for any columns from the right table. These are rows that an outer join will produce but an inner join will not.

For the LEFT JOIN just shown, rows in Country not matched by any CountryLanguage rows correspond to countries for which no language is listed. These are the extra rows not produced by an inner join. Any columns that come from CountryLanguage are set to NULL. The NULL values serve two purposes:

• For a query such as the preceding one that displays both matches and non-matches, the NULL values identify which output rows represent non-matches.

• If you're interested only in non-matches, you can add a condition that restricts the output to only those rows that contain these NULL values.

For example, the question "Which countries have no languages listed?" is equivalent to asking which country codes in the Country table aren't matched by codes in the CountryLanguage table. To answer the question, write a LEFT JOIN and require row combinations to have NULL in the right table column:

 mysql> SELECT Name, Language     -> FROM Country LEFT JOIN CountryLanguage ON Code = CountryCode     -> WHERE CountryCode IS NULL; +----------------------------------------------+----------+ | Name                                         | Language | +----------------------------------------------+----------+ | Antarctica                                   | NULL     | | Bouvet Island                                | NULL     | | British Indian Ocean Territory               | NULL     | | South Georgia and the South Sandwich Islands | NULL     | | Heard Island and McDonald Islands            | NULL     | | French Southern territories                  | NULL     | +----------------------------------------------+----------+ 

Because Language is always NULL in the output, you probably would not bother displaying it:

 mysql> SELECT Name     -> FROM Country LEFT JOIN CountryLanguage ON Code = CountryCode     -> WHERE CountryCode IS NULL; +----------------------------------------------+ | Name                                         | +----------------------------------------------+ | Antarctica                                   | | Bouvet Island                                | | British Indian Ocean Territory               | | South Georgia and the South Sandwich Islands | | Heard Island and McDonald Islands            | | French Southern territories                  | +----------------------------------------------+ 

As mentioned earlier, the order in which you name the tables in the FROM clause doesn't matter for an inner join. The query results are the same regardless of which table you name first. That is not true for an outer join; the output depends very much on the order in which the tables are named. With a LEFT JOIN, the reference table should be listed on the left and the table from which rows might be missing should be listed on the right.

If you're looking only for matches between tables, you can do so with either an inner or outer join. In such cases, it's better to use an inner join because that allows the MySQL optimizer to choose the most efficient order for processing the tables. Outer joins require that the reference table be processed first, which might not be the most efficient order.

12.3.2. Writing RIGHT JOIN Queries

A right join is another type of outer join, written using the RIGHT JOIN keywords. Every right join corresponds to an equivalent left join. The only difference is that the roles of the tables in a right join are reversed relative to the roles in a left join. That is, the right table is the reference table, so a RIGHT JOIN produces a result for each row in the right table, whether or not it has any match in the left table. Thus, if you write a LEFT JOIN as follows:

 SELECT ... FROM t1 LEFT JOIN t2 ON t1_column = t2_column ... 

You can convert it to a RIGHT JOIN like this:

 SELECT ... FROM t2 RIGHT JOIN t1 ON t2_column = t1_column ... 

For example, a LEFT JOIN query to display countries in the Country table that have no languages listed in the CountryLanguage table can be written this way:

 mysql> SELECT Name     -> FROM Country LEFT JOIN CountryLanguage ON Code = CountryCode     -> WHERE CountryCode IS NULL; +----------------------------------------------+ | Name                                         | +----------------------------------------------+ | Antarctica                                   | | Bouvet Island                                | | British Indian Ocean Territory               | | South Georgia and the South Sandwich Islands | | Heard Island and McDonald Islands            | | French Southern territories                  | +----------------------------------------------+ 

The corresponding RIGHT JOIN looks like this:

 mysql> SELECT Name     -> FROM CountryLanguage RIGHT JOIN Country ON CountryCode = Code     -> WHERE CountryCode IS NULL; +----------------------------------------------+ | Name                                         | +----------------------------------------------+ | Antarctica                                   | | Bouvet Island                                | | British Indian Ocean Territory               | | South Georgia and the South Sandwich Islands | | Heard Island and McDonald Islands            | | French Southern territories                  | +----------------------------------------------+ 

Syntactically, converting a left join to a right join requires only that you reverse the order in which you name the tables. It isn't necessary to also reverse the order in which you name the columns in the ON clause, but it can help make the query clearer to name the columns in the same order as the tables in which they appear.