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 ...