Java Reference
In-Depth Information
Orders o ON c.Customer_number = o.Customer_Number;
The result set this query generates is shown in Table 3-14 . Note the NULLs listed
under order date where the customer hasn't actually placed an order.
Table 3-14: Results of Left Outer Join
Last_Name
First_Name
Order_Date
Corleone
Michael
<NULL>
Corleone
Fredo
12/8/01
Corleone
Sonny
<NULL>
Corleone
Francis
12/9/01
Corleone
Vito
12/9/01
Hagen
Tom
<NULL>
Adams
Kay
12/10/01
Coppola
Francis
<NULL>
Puzo
Mario
<NULL>
RIGHT OUTER JOIN
It is important to note that "left" and "right" are completely dependent on the order of
the tables in the SQL sta tement, so you can turn this into a RIGHT OUTER JOIN by
reversing the order of the tables in the JOIN command. Here's an example:
SELECT c.Last_Name + ', ' + c.First_Name AS Customer,
o.Order_Date
FROM ORDERS o RIGHT OUTER JOIN
CUSTOMERS c ON c.customer_number = o.customer_number;
OUTER JOIN commands can also be written in a shorthand similar to the form we
use for our INNER JOIN. The form for the LEFT OUTER JOIN uses the "*=" operator,
as shown here:
SELECT c.Last_Name + ', ' + c.First_Name AS Customer,
o.Order_Date
FROM CUSTOMERS c, ORDERS o
WHERE c.customer_number *= o.customer_number;
The form for the RIGHT OUTER JOIN uses the "=*" operator as follows:
SELECT c.Last_Name + ', ' + c.First_Name AS Customer,
Search WWH ::




Custom Search