Showing the Background: Outer Joins

The joins we've discussed so far include only rows that satisfy the join condition in the results. Occasionally, you may want to display the rows of one table that do not satisfy the join condition. An outer join shows you the join rows against the background of rows that did not meet the join conditions. It is convenient for putting results in context and makes visual scans easy. You can use it for finding books that did not sell or authors with no books.

SQL-92 provides language for retrieving either the LEFT (first-named) table for an outer join or the RIGHT (second-named) table. A FULL outer join lets you see non-matching rows from both tables.

Left Outer Join

Recall that the query for authors who live ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.