Java Reference
In-Depth Information
o.Order_Date
FROM ORDERS o, CUSTOMERS c
WHERE o.customer_number =* c.customer_number;
In the shorthand version, the type of JOIN depends on both the order of
the tables in the FROM clause and the position of the asterisk in the *=
operator.
Note
FULL OUTER JOIN
A "full outer join" includes all unmatched rows from both tables in the result. For
example, to find any orders in the Orders Table with customer numbers that do not
match any entries in our Customers Table, you can execute a Full Outer Join to show
all the entries in both tables. Here's an example:
SELECT c.Last_Name, c.First_Name, o.Order_Date
FROM Customers c FULL OUTER JOIN
Orders o ON c.Customer_number = o.Customer_Number;
The result set generated by this join is the same as the results shown in
Table 3-14
,
since all orders have a corresponding customer. However, if, for some reason, an
order placed on 12/12/01existed in the Orders Table with no corresponding entry in
the Customers Table, the additional row shown at the bottom of
Table 3 -15
would be
generated.
Table 3-15:
Results of FULL 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>
<NULL>
<NULL>
12/12/01
Using NOT EXISTS
