This article includes a list ofgeneral references, butit lacks sufficient correspondinginline citations. Please help toimprove this article byintroducing more precise citations.(July 2022) (Learn how and when to remove this message)

Set operations inSQL is 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, whichmathematically correspond to the concepts ofunion,intersection andset difference.

InSQL theUNION clause combines the results of two SQL queries into a singletable of all matchingrows. The two queries must result in the same number ofcolumns and compatibledata types in order to unite. Any duplicate records are automatically removed unlessUNION ALL is used.

UNION can be useful indata warehouse applications 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.

Given these two tables:

Executing this statement:

SELECT*FROMsales2005UNIONSELECT*FROMsales2006;

yields this result set, though the order of the rows can vary because noORDER BY clause was supplied:

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*FROMsales2005UNIONALLSELECT*FROMsales2006;

would give these results, again allowing variance for the lack of anORDER BY statement:

The discussion offull outer joins also has an example that usesUNION.

The 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 appear${\displaystyle min(X,Y)}$ times in the result set.

The following exampleINTERSECT query returns all rows from the Orders table where Quantity is between 50 and 100.

SELECT*FROMOrdersWHEREQuantityBETWEEN1AND100INTERSECTSELECT*FROMOrdersWHEREQuantityBETWEEN50AND200;

The 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 appear${\displaystyle max(X-Y,0)}$ times in the result set.

Notably, the Oracle platform provides aMINUS operator which is functionally equivalent to theSQL standardEXCEPT DISTINCT operator.^[3]

The 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*FROMOrdersWHEREQuantityBETWEEN1AND100EXCEPTSELECT*FROMOrdersWHEREQuantityBETWEEN50AND75;

The following example is equivalent to the above example but without using theEXCEPT operator.

SELECTo1.*FROM(SELECT*FROMOrdersWHEREQuantityBETWEEN1AND100)o1LEFTJOIN(SELECT*FROMOrdersWHEREQuantityBETWEEN50AND75)o2ONo1.id=o2.idWHEREo2.idISNULL

• Union (set theory)
• Join (SQL)
• SQL:2003
• Select (SQL)

• 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

• SQL keywords

• Articles lacking in-text citations from July 2022
• All articles lacking in-text citations
• Articles with short description
• Short description matches Wikidata
• Articles with example SQL code