This article includes a list of generalreferences,butit lacks sufficient correspondinginline citations.(July 2022) |
Set operationsinSQLis a type of operations which allow the results of multiple queries to be combined into a singleresult set.[1]
Set operators in SQL includeUNION
,INTERSECT
,andEXCEPT
,whichmathematicallycorrespond to the concepts ofunion,intersectionandset difference.
UNION operator
editInSQLtheUNION
clause combines the results of two SQL queries into a singletableof all matchingrows.The two queries must result in the same number ofcolumnsand compatibledata typesin order to unite. Any duplicate records are automatically removed unlessUNION ALL
is used.
UNION
can be useful indata warehouseapplications where tables are not perfectlynormalized.[2]A simple example would be a database having tablessales2005
andsales2006
that have identical structures but are separated because of performance considerations. AUNION
query could combine results from both tables.
Note thatUNION 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 thatUNION ALL
may be much faster than plainUNION
.
Examples
editGiven these two tables:
person | amount |
---|---|
Joe | 1000 |
Alex | 2000 |
Bob | 5000 |
person | amount |
---|---|
Joe | 2000 |
Alex | 2000 |
Zach | 35000 |
Executing this statement:
SELECT*FROMsales2005
UNION
SELECT*FROMsales2006;
yields this result set, though the order of the rows can vary because noORDER 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:
SELECT*FROMsales2005
UNIONALL
SELECT*FROMsales2006;
would give these results, again allowing variance for the lack of anORDER BY
statement:
person | amount |
---|---|
Joe | 1000 |
Joe | 2000 |
Alex | 2000 |
Alex | 2000 |
Bob | 5000 |
Zach | 35000 |
The discussion offull outer joinsalso has an example that usesUNION
.
INTERSECT operator
editThe SQLINTERSECT
operator takes the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal theINTERSECT
operator does not distinguish betweenNULLs
.TheINTERSECT
operator removes duplicate rows from the final result set. TheINTERSECT 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 appeartimes in the result set.
Example
editThe following exampleINTERSECT
query returns all rows from the Orders table where Quantity is between 50 and 100.
SELECT*
FROMOrders
WHEREQuantityBETWEEN1AND100
INTERSECT
SELECT*
FROMOrders
WHEREQuantityBETWEEN50AND200;
EXCEPT operator
editThe SQLEXCEPT
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, theEXCEPT
operator does not distinguish betweenNULLs
.TheEXCEPT 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 appeartimes in the result set.
Notably, the Oracle platform provides aMINUS
operator which is functionally equivalent to theSQL standardEXCEPT DISTINCT
operator.[3]
Example
editThe following exampleEXCEPT
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.
SELECT*
FROMOrders
WHEREQuantityBETWEEN1AND100
EXCEPT
SELECT*
FROMOrders
WHEREQuantityBETWEEN50AND75;
Example
editThe following example is equivalent to the above example but without using theEXCEPT
operator.
SELECTo1.*
FROM(
SELECT*
FROMOrders
WHEREQuantityBETWEEN1AND100)o1
LEFTJOIN(
SELECT*
FROMOrders
WHEREQuantityBETWEEN50AND75)o2
ONo1.id=o2.id
WHEREo2.idISNULL
See also
editReferences
edit- ^"The UNION [ALL], INTERSECT, MINUS Operators".Oracle.Retrieved14 July2016.
- ^"a
UNION ALL
views technique for managing maintenance and performance in your large data warehouse environment... ThisUNION 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) - ^"E071-03,
EXCEPT DISTINCT
table operator: UseMINUS
instead ofEXCEPT DISTINCT
""Oracle Compliance To Core SQL:2003".Docs.oracle.com.Retrieved7 July2022.
External links
edit- MSDN documentation on UNION in Transact-SQL for SQL Server
- Naming of select list items in set operations
- UNION in MySQL with Examples
- UNION in MySQL
- UNION Clause in PostgreSQL
- SQL UNION and UNION ALL
- Sort order within UNION statement
- Designing a data flow that loads a warehouse table
- Oracle 11g documentation for UNION (ALL), INTERSECT and MINUS
- SQL Set Operators