Learning SQL, 3rd 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, the inventory table contains a row for every film available for rental, but of the 1,000 rows in the film table, only 958 have one or more rows in the inventory table. The other 42 films are not available for rental (perhaps they are new releases due to arrive in a few days), so these film IDs cannot be found in the inventory table. The following query counts the number of available copies of each film by joining these two tables:

 mysql> SELECT f.film_id, f.title, count(*) num_copies     -> FROM film f     ->   INNER JOIN inventory i     ->   ON f.film_id = i.film_id     -> GROUP BY f.film_id, f.title; +---------+-----------------------------+------------+ | film_id | title                       | num_copies | +---------+-----------------------------+------------+ |       1 | ACADEMY DINOSAUR            |          8 | |       2 | ACE GOLDFINGER              |          3 | |       3 | ADAPTATION HOLES            |          4 | |       4 | AFFAIR PREJUDICE            |          7 | ... |      13 | ALI FOREVER                 |          4 | |      ...