Set operations allow the results of multiple queries to be combined into a single
result set
An SQL result set is a set of rows from a database, as well as metadata about the query such as the column names, and the types and sizes of each column. Depending on the database system
In computing, a database is an organized collection of ...
. Set operators include
UNION
,
INTERSECT
, and
EXCEPT
.
UNION operator
In
SQL the
UNION
clause combines the results of two SQL queries into a single
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
of all matching
rows. The two queries must result in the same number of
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression membe ...
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
In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis and is considered a core component of business intelligence. DWs are central repositories of integra ...
applications where tables are not perfectly
normalized. 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
.
Examples
Given these two tables:
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:
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:
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, but if a row appears X times in the first query and Y times in the second, it will appear
times in the result set.
Example
The following example
INTERSECT
query returns all rows from the Orders table where Quantity is between 50 and 100.
SELECT *
FROM Orders
WHERE Quantity BETWEEN 1 AND 100
INTERSECT
SELECT *
FROM Orders
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. For purposes of row elimination and duplicate removal, the
EXCEPT
operator does not distinguish between
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
times in the result set.
Notably, the Oracle platform provides a
MINUS
operator which is functionally equivalent to the
SQL standard EXCEPT DISTINCT
operator.
["E071-03, ]EXCEPT DISTINCT
table operator: Use MINUS
instead of EXCEPT DISTINCT
"
Example
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.
SELECT *
FROM Orders
WHERE Quantity BETWEEN 1 AND 100
EXCEPT
SELECT *
FROM Orders
WHERE Quantity BETWEEN 50 AND 75;
Example
The following example is equivalent to the above example but without using the
EXCEPT
operator.
SELECT o1.*
FROM (
SELECT *
FROM Orders
WHERE Quantity BETWEEN 1 AND 100) o1
LEFT JOIN (
SELECT *
FROM Orders
WHERE Quantity BETWEEN 50 AND 75) o2
ON o1.id = o2.id
WHERE o2.id IS NULL
See also
*
Union (set theory)
In set theory, the union (denoted by ∪) of a collection of sets is the set of all elements in the collection. It is one of the fundamental operations through which sets can be combined and related to each other.
A refers to a union of ze ...
*
Join (SQL)
A join clause in SQL – corresponding to a join operation in relational algebra – combines columns from one or more tables into a new table. Informally, a join stitches two tables and puts on the same row records with matching fields : INNER, ...
*
SQL:2003
*
Select (SQL)
The SQL SELECT statement returns a result set of records, from one or more tables.
A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data ...
References
External links
MSDN documentation on UNION in Transact-SQL for SQL ServerUNION in MySQL with ExamplesSQL UNION and UNION ALL
{{SQL
SQL keywords
Articles with example SQL code