Oracle Certification - SQL Fundamentals I - Set Operators
Set Operators
Set Operators determine how the results of two or more queries are combined into a single result set, or compound query. The set operators perform standard operations, that are often depicted in mathematics with a Venn Diagram.
UNION
UNION combines the rows from two queries, sorting them and removing any duplicates.
UNION ALL
UNION ALL combines the rows from two queries, without performing a sort or the elimination of duplicates.
INTERSECT
INTERSECT returns the common values from two queries, sorting the results and removing any duplicates.
MINUS
MINUS (or EXCEPT is ISO SQL) returns rows that appear in the first result set which are not present in the second result set, again the results are sorted and any duplicates are removed.
General Rules
The following general rules apply:
- Set Operators are applied in the order they appear
- Brackets can override the precedence of operators
- Each query must return the same number of columns
- The columns must be in the same sequence
- The columns must be of compatible data types
- The column names / aliases do not have to be the same
- The column names / aliases of the first query will take precedence
- The ORDER BY clause can only appear at the end of the query
UNION
UNION ALL
The following SQL statement demonstrates that UNION ALL just combines two queries, without sorting or removing duplicates. First of all the results from the two queries without the UNION ALL:
And then combining the results with a UNION ALL(Note that the rows are returned in the order they are retrieved from the tables):
The ORDER BY clause can be appended to the very end of the query to sort the results:
UNION
By comparison using the UNION set operator the duplicates are removed, and the query is automatically sorted:
INTERSECT
The INTERSECT set operator will only return rows that are common in both queries, and it also removes any duplicates and sorts the output:
MINUS
MINUS returns only the results from the first query, that do not appear in the second. Additionally any duplicates are removed and the results are sorted:
Precedence
The following more complex compound query returns the results of the INTERSECT example with those of the MINUS example. Note the use of the brackets to override the default precedence to produce the desired results: