Java Reference
In-Depth Information
boss.last_name + ', ' + boss.first_name AS Boss
FROM EMPLOYEES e, employees boss
WHERE e.supervisor *= boss.employee_id;
This returns one additional row; the Employee_ID of Vito's supervisor does not appear in the
Employees Table, so his boss is shown as
<NULL>
in the example of
Figure 9-10
.
Figure 9-10:
Using an Outer Self-Join
In addition to using joins, you can combine data from two separate sources using the
UNION
operator.
The
next section
describes the
UNION
operator.
Using the UNION Operator to Combine Queries
Another way to combine data from two separate sources is to use the
UNION
operator. The default
action of the
UNION
operator is to combine the results of two or more queries into a single query and to
eliminate any duplicate rows. When
ALL
is used with
UNION
, duplicate rows are not eliminated.
In
Figure 9-11
, the first query returns the names and addresses of all the Corleones, and the second
returns all customers in New Jersey. The
UNION
operator combines the results, removing the duplicate
records that are generated for Corleones in New Jersey.