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 | +----------------+----------------+
MySQL takes this concept one step further by using a natural outer join. A natural outer join will combine the rows from two tables that have identical column names with identical types and identical values:
SELECT my_prod.name FROM my_prod NATURAL LEFT JOIN their_prod
This natural join will list all product names with identical entries
in the my_prod
and their_prod
tables.