Java Reference
In-Depth Information
Figure 9-11:
Using the UNION operator to combine two result sets
Understanding 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 of 100 rows each is10,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.
You can use
ORDER BY
to sort the combined answer set by adding the
ORDER BY
clause after the last
query. You do not have to use the same column in each query. Only the column counts and column
types needs to match. If you create a
UNION
of two result sets with different columns, you have to
apply the
ORDER BY
clause using the column number. An example of this usage is shown in
Figure
9-12
.
Figure 9-12:
Using ORDER BY on a UNION
