Joins that produce a joined table that includes all rows from the outer table, regardless of whether or not the on clause produces matching rows, are called outer joins.
Inner joins and equijoins produce a result set that includes only the rows from the tables where there are matching values in the join clause. There are times, however, when you want to include not only the matching rows, but also the rows from one of the tables where there are no matching rows in the second table. This type of join is an outer join. In an outer join, rows that do not meet the on clause conditions are included in the joined table with null-supplied values for the inner table of the outer join. The inner table is also referred to as the null-supplying member.
SAP recommends that your applications use ANSI outer joins because they unambiguously specify whether the on or where clause contains the predicate.
ANSI outer join syntax is:
select select_list
from table1 {left | right} [outer] join table2
on predicate
[join restriction]
Left joins retain all the rows of the table reference listed on the left of the join clause; right joins retain all the rows of the table reference on the right of the join clause. In left joins, the left table reference is referred to as the outer table, or row-preserving table.
select au_fname, au_lname, pub_name from authors left join publishers on authors.city = publishers.city
au_fname au_lname pub_name --------- ---------- ------------- Johnson White NULL Marjorie Green NULL Cheryl Carson Algodata Infosystems . . . Abraham Bennet Algodata Infosystems . . . Anne Ringer NULL Albert Ringer NULL (23 rows affected)
The result set contains all the authors from the authors table. The authors who do not live in the same city as their publishers produce null values in the pub_name column. Only the authors who live in the same city as their publishers, Cheryl Carson and Abraham Bennet, produce a non-null value in the pub_name column.
You can rewrite a left outer join as a right outer join by reversing the placement of the tables in the from clause. Also, if the select statement specifies “select *”, you must write an explicit list of all the column names, otherwise, the columns in the result set may not be in the same order for the rewritten query.
Here is the previous example rewritten as a right outer join, which produces the same result set as the left outer join above:
select au_fname, au_lname, pub_name from publishers right join authors on authors.city = publishers.city