Java Reference
In-Depth Information
Last_Name
First_Name
Boss
Corleone
Vito
<NULL>
Hagen
Tom
Corleone, Michael
Adams
Kay
Corleone, Michael
Coppola
Francis
Corleone, Michael
Cartesian Products
Cartesian products, or cross products, are something you normally want to avoid.
The Cartesian product of a Join occurs when every record in one table is joined on
every record of the other, so the Cartesian product of two tables 100-rows long is
10,000 rows.
Cartesian products are normally an error, caused by a bad or nonexistent WHERE
clause. In the case of a small table like the ones in our examples, this is not a
major problem; but on a large database, the time taken to generate cross products
of thousands of rows can be significant.
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 the following example, the first query returns the names and addresses of all the
Corleones; 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:
SELECT First_Name, Last_Name, Street, City, State
FROM Customers
WHERE Last_Name = 'Corleone'
UNION
SELECT First_Name, Last_Name, Street, City, State
FROM Customers
WHERE State = 'NJ'
ORDER BY Last_Name, First_Name;
