Learning SQL, 2nd Edition

Chapter 10. Joins Revisited

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

Outer Joins

In all the examples thus far that have included multiple tables, we haven’t been concerned that the join conditions might fail to find matches for all the rows in the tables. For example, when joining the account table to the customer table, I did not mention 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 |
|          7 |       3 |
|          8 |       3 |
|         10 |       4 |
|         11 |       4 |
|         12 |       4 |
|         13 |       5 |
|         14 |       6 |
|         15 |       6 |
|         17 |       7 |
|         18 |       8 |
|         19 |       8 |
|         21 |       9 |
|         22 |       9 |
|         23 |       9 |
|         24 |      10 |
|         25 |      10 |
|         27 |      11 |
|         28 |      12 |
|         29 |      13 |
+------------+---------+
24 rows in set (1.50 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:

mysql> SELECT cust_id ...