Java Reference
In-Depth Information
Inner and Outer Joins
The Joins discussed so far have been
Inner Joins
. An Inner Join exists between two
tables and includes only rows with matching rows in the both tables. The easiest way
to understand the terminology of Inner and Outer Joins is to look at
Figure 3-1
, where
the Customer_Number columns in the Customers and Orders Tables have been
overlapped or "Joined."
Figure 3-1:
Tables joined on customer number
The two tables are shown in the rounded boxes; the joined fields are shaded.
Using an Inner Join, as shown in the last example, you can only list customers who
have placed an order, so their customer numbers fall into the shaded area of
Figure3-1
. If you want a list of all customers, together with the dates of any orders
they have placed, you can't get there with an Inner Join.
An
Outer Join
can include not only records that are inside the union of the sets or
tables but records that are outside the union of the sets. In other words, in addition to
the set members that share customer numbers, you can get customers in the lower,
or "Outer," part of the joined tables.
These are the three different types of Outer Joins:
LEFT OUTER JOIN (*=)
RIGHT OUTER JOIN (=*)
FULL OUTER JOIN
The terms LEFT, RIGHT, and FULL describe which of the tables' unmatched columns
to include in the Join relative to the order in which the tables appear in the JOIN
command.
LEFT OUTER JOIN
The LEFT OUTER JOIN operator includes all rows from the left side of the join. This
includes all the customers who have not placed any orders, as shown here:
SELECT c.Last_Name, c.First_Name, o.Order_Date
FROM Customers c LEFT OUTER JOIN
