Skip to Main Content
Learning SQL
book

Learning SQL

by Alan Beaulieu
August 2005
Beginner content levelBeginner
312 pages
8h 23m
English
O'Reilly Media, Inc.
Content preview from 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: ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL

Learning SQL

Guy Vaccaro
Learning SQL

Learning SQL

Ben Forta
Learning SQL

Learning SQL

Alan Beaulieu

Publisher Resources

ISBN: 0596007272Supplemental ContentErrata Page