Outer Joins

Whereas an INNER JOIN will find combinations of matching rows from joined tables, the OUTER
JOIN also finds the instances where a row in one table has no match in another table. An OUTER JOIN
find matches (just like an INNER JOIN), but also identifies mismatches.

Two forms of outer joins are LEFT JOIN and RIGHT JOIN. These do not use the comma separator or
the INNER JOIN keywords. They each answer the same kinds of questions, but differ slightly in their
syntax. That is a LEFT JOIN can always be re-written into a 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.)

 Left Outer Join
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.
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 provide information about 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. As illustrated below, the LEFT
JOIN will result in a list that shows where there were empty (NULL) values for the Language;

mysql> SELECT Country.Name, CountryLanguage.Language
-> FROM Country
-> LEFT JOIN CountryLanguage
-> ON Country.Code = CountryLanguage.CountryCode;


+-----------------------------+------------------+
| Name | Language |
+-----------------------------+------------------+
| Aruba | Dutch |
| Aruba | English |
| Aruba | Papiamento |
| Aruba | Spanish |
| Afghanistan | Balochi |
...
| Antarctica | NULL |
| French Southern territories | NULL |
| Antigua and Barbuda | Creole English |
| Antigua and Barbuda | English |
| Australia | Arabic |
| Australia | Canton Chinese |
| Australia | English |
| Australia | German |
...
990 rows in set (#.## sec)


Filter Out Matches

Furthermore, with an appropriate WHERE clause, an OUTER JOIN can filter out matches to display only
the mismatches. The following query can be used, with a LEFT JOIN which requires row combinations
to have NULL in the right table;

mysql> SELECT Country.Name, Country.Language
-> FROM Country LEFT JOIN CountryLanguage
-> ON Country.Code = CountryLanguage.CountryCode
-> WHERE CountryLanguage.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 |
+----------------------------------------------+----------+
6 rows in set (#.## sec)