Java Reference
In-Depth Information
You can use ORDER BY, as shown, to sort the combined answer set by adding the
ORDER BY clause after the last query. Here is the result:
First_Name
Last_Name
Street
City
State
Kay
Adams
109 Maple
Newark
NJ
Francis
Corleone
17 Main
New York
NY
Fredo
Corleone
19 Main
New York
NY
Michael
Corleone
123 Pine
New York
NY
Sonny
Corleone
123 Walnut
Newark
NJ
Vito
Corleone
23 Oak St
Newark
NJ
Tom
Hagen
37 Chestnut
Newark
NJ
You do not have to use the same columns in each query. Only the column counts and
column types need to match. However, if you create a UNION of two result sets with
different columns, you have to apply the ORDER BY clause using the column
number.
EXCEPT operator
The EXCEPT operator creates a result set by including all rows that the first query
returns but not rows that the second query returns. The default version eliminates all
duplicate rows; EXCEPT ALL does not. The following statement will return the names
and addresses of all Corleones except those living in New Jersey:
SELECT First_Name, Last_Name, Street, City, State
FROM Customers
WHERE Last_Name = 'Corleone'
EXCEPT
SELECT First_Name, Last_Name, Street, City, State
FROM Customers
WHERE State = 'NJ'
INTERSECT operator
The INTERSECT operator creates a result set by including only rows that exist in
both queries and eliminating all duplicate rows. When you use ALL with INTERSECT,
the duplicate rows are not eliminated. The following statement will return the names
and addresses of Corleones living in New Jersey:
SELECT First_Name, Last_Name, Street, City, State
FROM Customers
