UNION operator

In SQL the UNION operator combines the results of two SQL queries into a single table of all matching rows. The two queries must have the same number of columns and compatible data types in order to join them. Any duplicate records are automatically removed unless UNION ALL is used.

UNION can be useful in data warehouse applications where tables aren't perfectly normalized.^[1] 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 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.

Examples

Given these two tables:

sales2005

person	amount
Joe	1000
Alex	2000
Bob	5000

sales2006

person	amount
Joe	2000
Alex	2000
Zach	35000

Executing this statement:

SELECT * FROM sales2005
UNION
SELECT * FROM sales2006;

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:

SELECT * FROM sales2005
UNION ALL
SELECT * FROM sales2006;

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.

INTERSECT operator

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 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.

Example

The following example INTERSECT query returns all rows from the Order table where Quantity is between 50 and 100.

SELECT *
FROM Order
WHERE Quantity BETWEEN 1 AND 100

INTERSECT

SELECT *
FROM Order
WHERE Quantity BETWEEN 50 AND 200;

EXCEPT operator

The SQL EXCEPT operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. The EXCEPT ALL operator does not remove duplicates. For purposes of row elimination and duplicate removal, the EXCEPT operator does not distinguish between NULLs.

Notably, the Oracle platform provides a MINUS operator which is functionally equivalent to the SQL standard EXCEPT DISTINCT operator [1].

Example

The following example EXCEPT query returns all rows from the Order table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.

SELECT *
FROM Order
WHERE Quantity BETWEEN 1 AND 100

EXCEPT

SELECT *
FROM Order
WHERE Quantity BETWEEN 50 AND 75;

See also

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

References

External links

• SQL Tutorial
• MSDN documentation on UNION in Transact-SQL for SQL Server
• UNION in MySQL
• SQL UNION and UNION ALL
• Designing a data flow that loads a warehouse table

Database management systems
Database models · Database normalization · Database storage · Distributed DBMS · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Transaction processing
Concepts	Database · ACID · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key
Objects	Trigger · View · Table · Cursor · Log · Transaction · Index · Stored procedure · Partition
SQL	Select · Insert · Update · Merge · Delete · Join · Union · Create · Drop · Begin work · Commit · Rollback · Truncate · Alter
Implementations	Relational · Flat file · Deductive · Dimensional · Hierarchical · Object-oriented · Object-relational · Temporal · XML data stores
Components	Concurrency control · Query language · Query optimizer · Query plan · ODBC · JDBC
Database products: Object-oriented (comparison) · Relational (comparison)

Donate to Wikimedia