OUTER JOIN, LEFT JOIN and RIGHT JOIN are used to combine result sets from two queries made to different tables into one result set. This is useful for combining queries where one table may or may not contain a relevant matching record.

This example will use the 'world.sql' sample database provided by http:dev.myql.com/doc

If we want a list of every country that also has a city with the same name, this is easily accomplished with the following simple query: 

mysql> SELECT co.name, ci.name
    -> FROM country co, city ci
    -> WHERE co.name LIKE ci.name
    -> AND co.code = ci.countrycode;
+------------+------------+
| name       | name       |
+------------+------------+
| Djibouti   | Djibouti   |
| Gibraltar  | Gibraltar  |
| Kuwait     | Kuwait     |
| Macao      | Macao      |
| San Marino | San Marino |
| Singapore  | Singapore  |
+------------+------------+
6 rows in set (0.00 sec)

But what if we want a list of all countries, with a matching city name being optional. A LEFT JOIN will accomplish this by showing all results from the left table, regardless of what is returned on the right. 

mysql> SELECT co.name, ci.name
    -> FROM country co
    -> LEFT JOIN city ci ON
    -> co.name LIKE ci.name
    -> AND co.code = ci.countrycode
    -> ORDER BY ci.name DESC LIMIT 20;
+------------------------+------------+
| name                   | name       |
+------------------------+------------+
| Singapore              | Singapore  |
| San Marino             | San Marino |
| Macao                  | Macao      |
| Kuwait                 | Kuwait     |
| Gibraltar              | Gibraltar  |
| Djibouti               | Djibouti   |
| Qatar                  | NULL       |
| Costa Rica             | NULL       |
| Kiribati               | NULL       |
| Taiwan                 | NULL       |
| Bosnia and Herzegovina | NULL       |
| French Guiana          | NULL       |
| Niger                  | NULL       |
| El Salvador            | NULL       |
| Aruba                  | NULL       |
| Eritrea                | NULL       |
| Latvia                 | NULL       |
| Vanuatu                | NULL       |
| Switzerland            | NULL       |
| Iraq                   | NULL       |
+------------------------+------------+
20 rows in set (2.85 sec)

Here we have a complete country list, alongside a matching city name where applicable.

The same can be done with a RIGHT JOIN by simply switching the query on the left with the one on the right. 

mysql> SELECT co.name, ci.name
    -> FROM city ci
    -> RIGHT JOIN country co ON
    -> ci.name LIKE co.name
    -> AND ci.countrycode LIKE co.code
    -> ORDER BY ci.name DESC LIMIT 20;
+------------------------+------------+
| name                   | name       |
+------------------------+------------+
| Singapore              | Singapore  |
| San Marino             | San Marino |
| Macao                  | Macao      |
| Kuwait                 | Kuwait     |
| Gibraltar              | Gibraltar  |
| Djibouti               | Djibouti   |
| Qatar                  | NULL       |
| Costa Rica             | NULL       |
| Kiribati               | NULL       |
| Taiwan                 | NULL       |
| Bosnia and Herzegovina | NULL       |
| French Guiana          | NULL       |
| Niger                  | NULL       |
| El Salvador            | NULL       |
| Aruba                  | NULL       |
| Eritrea                | NULL       |
| Latvia                 | NULL       |
| Vanuatu                | NULL       |
| Switzerland            | NULL       |
| Iraq                   | NULL       |
+------------------------+------------+
20 rows in set (2.90 sec)

Remember, LEFT JOIN always shows all records from the left table, while a RIGHT join always shows records from the right table, with the joined table in either case being optional.

You might also be interested in