Databases Reference
In-Depth Information
7.1.6 Combining Statement Results
You can combine the results from different SELECT statements using the
UNION, EXCEPT, and INTERSECT keywords. In each case, both SELECT state-
ments you are trying to combine must have:
The same number of columns.
▲
Columns in the same order.
▲
Columns with compatible (but not necessarily the same) data types.
▲
The UNION operator combines the results of two queries into one. The final
result is a list of unique rows unless you also include the ALL keyword, with
the syntax UNION ALL. UNION ALL returns all rows, including duplicate rows.
First, let's look at a sample query:
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 137
This gives you the following result:
HQCITY
New York
Los Angeles
Los Angeles
New York
The next query is:
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 186
The result is:
HQCITY
Chicago
New York
Finally, combining the two with UNION, as in:
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 137
UNION
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 186
gives you, as a final result:
HQCITY
Chicago
Los Angeles
New York
As you can see, the result lists all three unique cities, listing each one only once.
The EXCEPT keyword returns results in the first query that do not appear
in the second. In this case, we're going to use a different second query to get
some different values for comparison. This time, the query is:
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 186
