Join Trees, a Crash Introduction
SELECT …
FROM Orders O,
Order_Details OD, Products P, Customers C,
Shipments S, Addresses A,
Code_Translations ODT, Code_Translations OT
WHERE UPPER(C.Last_Name)
LIKE :Last_Name||'%'
AND UPPER(C.First_Name) LIKE
:First_Name||'%'
AND OD.Order_ID = O.Order_ID
AND O.Customer_ID = C.Customer_ID
AND OD.Product_ID = P.Product_ID(+)
AND OD.Shipment_ID = S.Shipment_ID(+)
AND S.Address_ID = A.Address_ID(+)
AND O.Status_Code = OT.Code
AND OT.Code_Type = 'ORDER_STATUS'
AND OD.Status_Code = ODT.Code
AND ODT.Code_Type =
'ORDER_DETAIL_STATUS'
AND O.Order_Date > :Now - 366
ORDER BY …;
•Each
table is a node, represented by its alias.
•Each
join is a link, with (usually downward-pointing)
arrows pointing toward
any side of the join that is unique.
•Midpoint
arrows point to optional side of
any outer join.
S P
O ODT
OD
A OT C
