Managing & Using MySQL, 2nd Edition

Outer Joins

MySQL supports a more powerful joining than the simple inner joins we saw earlier. Specifically, MySQL supports something called a left outer join (also known as simply an outer join), which you specify with the keywords LEFT JOIN. This type of join is similar to an inner join, except that it includes data in the first table named that does not match any in the second table. If you remember our author and book tables from earlier in the chapter, you will remember that our join would not list any authors who did not have books in our database. You may want to show entries from one table that have no corresponding data in the table to which you are joining. That is where an outer join comes into play:

SELECT book.title, author.name 
FROM author
LEFT JOIN book ON book.author = author.id

This query is similar to the inner join that you already understand:

SELECT book.title, author.name
FROM author, book
WHERE book.author = author.id

Note that an outer join uses the keyword ON instead of WHERE. The key difference in results is that the new syntax of the outer join will include authors such as Neil Gaiman, for whom no book is in our database. The results of the outer join would therefore look like this:

+----------------+----------------+ | book.title | author.name | +----------------+----------------+ | The Green Mile | Stephen King | | Guards, Guards!| Terry Pratchett| | Imzadi | Peter David | | Gold | Isaac Asimov | | Howling Mad | Peter David | | NULL | Neil Gaiman | +----------------+----------------+ ...