Learning SQL

Chapter 10. Joins Revisited

By now, you should be comfortable with the concept of the inner join, which was introduced in Chapter 5. This chapter will focus on other ways in which tables can be joined, including the outer join and the cross join.

Outer Joins

In all of the examples thus far that have included multiple tables, there hasn't been any concern that the join conditions might fail to find matches for all of the rows in the tables. For example, when joining the account table to the customer table, no mention was made of the possibility that a value in the cust_id column of the account table might not match a value in the cust_id column of the customer table. If that were the case, then some of the rows in one table or the other would be left out of the result set.

Just to be sure, let's check the data in the tables. Here are the account_id and cust_id columns from the account table:

mysql> SELECT account_id, cust_id
    -> FROM account;
+------------+---------+
| account_id | cust_id |
+------------+---------+
|          1 |       1 |
|          2 |       1 |
|          3 |       1 |
|          4 |       2 |
|          5 |       2 |
|          6 |       3 |
|          7 |       3 |
|          8 |       4 |
|          9 |       4 |
|         10 |       4 |
|         11 |       5 |
|         12 |       6 |
|         13 |       6 |
|         14 |       7 |
|         15 |       8 |
|         16 |       8 |
|         17 |       9 |
|         18 |       9 |
|         19 |       9 |
|         20 |      10 |
|         21 |      10 |
|         22 |      11 |
|         23 |      12 |
|         24 |      13 |
+------------+---------+
24 rows in set (0.04 sec)

There are 24 accounts spanning 13 different customers, with customer IDs 1 through 13 having at least one account. Here's the set of customer IDs from the customer table: ...