Set operations in SQL is a type of operations which allow the results of multiple queries to be combined into a single result set.[1]
Set operators in SQL include UNION
, INTERSECT
, and EXCEPT
, which mathematically correspond to the concepts of union, intersection and set difference.
In SQL the UNION
clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL
is used.
UNION
can be useful in data warehouse applications where tables are not perfectly normalized.[2] A simple example would be a database having tables sales2005
and sales2006
that have identical structures but are separated because of performance considerations. A UNION
query could combine results from both tables.
Note that UNION ALL
does not guarantee the order of rows. Rows from the second operand may appear before, after, or mixed with rows from the first operand. In situations where a specific order is desired, ORDER BY
must be used.
Note that UNION ALL
may be much faster than plain UNION
.
Given these two tables:
person | amount | |
---|---|---|
Joe | 1000 | |
Alex | 2000 | |
Bob | 5000 |
person | amount | |
---|---|---|
Joe | 2000 | |
Alex | 2000 | |
Zach | 35000 |
Executing this statement:
yields this result set, though the order of the rows can vary because no ORDER BY
clause was supplied:
person | amount | |
---|---|---|
Joe | 1000 | |
Alex | 2000 | |
Bob | 5000 | |
Joe | 2000 | |
Zach | 35000 |
Note that there are two rows for Joe because those rows are distinct across their columns. There is only one row for Alex because those rows are not distinct for both columns.
UNION ALL
gives different results, because it will not eliminate duplicates. Executing this statement:
would give these results, again allowing variance for the lack of an ORDER BY
statement:
person | amount | |
---|---|---|
Joe | 1000 | |
Joe | 2000 | |
Alex | 2000 | |
Alex | 2000 | |
Bob | 5000 | |
Zach | 35000 |
The discussion of full outer joins also has an example that uses UNION
.
The SQL INTERSECT
operator takes the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the INTERSECT
operator does not distinguish between [[Null (SQL)|NULLs]]
. The INTERSECT
operator removes duplicate rows from the final result set. The INTERSECT ALL
operator does not remove duplicate rows from the final result set, but if a row appears X times in the first query and Y times in the second, it will appear
min(X,Y)
The following example INTERSECT
query returns all rows from the Orders table where Quantity is between 50 and 100.
INTERSECT
SELECT *FROM OrdersWHERE Quantity BETWEEN 50 AND 200;
The SQL EXCEPT
operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. For purposes of row elimination and duplicate removal, the EXCEPT
operator does not distinguish between [[Null (SQL)|NULLs]]
. The EXCEPT ALL
operator does not remove duplicates, but if a row appears X times in the first query and Y times in the second, it will appear
max(X-Y,0)
Notably, the Oracle platform provides a MINUS
operator which is functionally equivalent to the SQL standard EXCEPT DISTINCT
operator.[3]
The following example EXCEPT
query returns all rows from the Orders table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.
Worded another way; the query returns all rows where the Quantity is between 1 and 100, apart from rows where the quantity is between 50 and 75.
EXCEPT
SELECT *FROM OrdersWHERE Quantity BETWEEN 50 AND 75;
The following example is equivalent to the above example but without using the EXCEPT
operator.
UNION ALL
views technique for managing maintenance and performance in your large data warehouse environment ... This UNION ALL
technique has saved many of my clients with issues related to time-sensitive database designs. These databases usually have an extremely volatile current timeframe, month, or day portion and the older data is rarely updated. Using different container DASD allocations, tablespaces, tables, and index definitions, the settings can be tuned for the specific performance considerations for these different volatility levels and update frequency situations." Terabyte Data Warehouse Table Design Choices - Part 2 (accessed on July 25, 2006)EXCEPT DISTINCT
table operator: Use MINUS
instead of EXCEPT DISTINCT
" Web site: Oracle Compliance To Core SQL:2003. Docs.oracle.com. 7 July 2022.