Kbase 15489: What is an SQL RIGHT OUTER JOIN vs. LEFT OUTER JOIN?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/1998 |
|
What is an SQL RIGHT OUTER JOIN vs. LEFT OUTER JOIN?
What is RIGHT OUTER JOIN vs LEFT OUTER JOIN?
--------------------------------------------
According to "Understanding The New SQL: A complete guide" by
Jim Melton, The LEFT OUTER JOIN perserves unmatched rows from the
left table, the one that precedes the keyword JOIN. A RIGHT
OUTER JOIN preserves unmatched rows from the right table, the
one that follows the keyword JOIN. The example below illustrates
a RIGHT OUTER JOIN:
select S.Rep-Name, S.Month-Quota[1], c.Cust-Num
from Customer c RIGHT OUTER JOIN Salesrep s ON
s.Month-Quota[1] = (c.Cust-Num * 100).
When trying to understand OUTER JOIN syntax, first identify
the LEFT and RIGHT table names. In this query, the CUSTOMER
table is the LEFT table, and SALESREP is the RIGHT table.
The above example code produces the following result set:
rep-name month-quota[1] cust-num
Brawn , Bubba B. 1600 16
Pitt , Dirk K. 1800 18
Donna Swindall 3800 38
Gilles Ehrer 1600 16
Harry Munvig 3800 38
Jan Loopsnel 2200 22
Kari Iso-Kauppinen 1800 18
Robert Roller 4200 42
Smith , Spike Louise 3000 30
There are as many records in the result as there are in the SALESREP
table. Had it been a LEFT OUTER JOIN, there would have had as many
records as there are in the customer table.
When you have a LEFT OUTER JOIN, you get as many records as there are
in the LEFT table.
When you have a RIGHT OUTER JOIN, you get as many records as there are
in the RIGHT table.
Note that if the query was defined as follows we would expect the same
number of records as in the customer table:
select s.rep\-name,s.month\-quota[1],c.cust\-num
from salesrep s right outer join customer c on s.month\-quota[1] =
(c.cust\-num * 100)
Progress Software Technical Support Note # 15489