Java Reference
In-Depth Information
you get to the SELECT. It can, however, be used in an ORDER BY. For example,
you can order the inventory table by retail as follows:
SELECT ID,Name,Description,Cost,Cost*1.6 AS Retail
FROM Inventory ORDER BY Retail;
Set operators
Set operators
allow you to combine ResultSets returned by different queries into a
single ResultSet. The main set operators are as follows:
UNION and UNION ALL return the combined results of two queries.
INTERSECT returns only the rows that both queries find.
EXCEPT returns the rows from the first query that are not present in the second.
Using UNION and UNION ALL
UNION ALL returns the results of two queries; while UNION does the same thing, but
it removes duplicate results. For example, you can use a UNION to combine the
results of a query for all customers with the last name "Adams" with a query for all
customers in New York with the last name "Corleone." Here's an example:
SELECT *
FROM Customers
WHERE Last_Name = 'Corleone' AND City = 'New York'
UNION
SELECT *
FROM Customers
WHERE Last_Name = 'Adams';
UNION, used by itself, returns the results of the two queries without any repetitions.
UNION ALL, on the other hand, returns the results of the two queries including all
repetitions.
Using INTERSECT and EXCEPT
The INTERSECT and EXCEPT operators adhere to the same syntax as the UNION
operator. You should check with the documentation for the DBMS you are using to
ensure that these operators are supported before using one of them.
Special-purpose operators
SQL also provides a number of operators to perform functions which, in most other
languages, require special procedural code. Since SQL is not a procedural language,
these are particularly useful features of the language.
