The UNION, INTERSECT, and EXCEPT clauses are used to combine or exclude like rows from two or more tables. They are useful when you need to combine the results from separate queries into one single result. They differ from a join in that entire rows are matched and, as a result, included or excluded from the combined result.
Overview
These operators can be used on any query; however, a couple simple of conditions must be met:
- The number and order columns must be the same in both queries.
- The data types must be the same or compatible.
UNION Operator
The Union
operator returns rows from both tables. If used by itself, UNION
returns a distinct list of rows. Using UNION ALL
, returns all rows from both tables. A UNION
is useful when you want to sort results from two separate queries as one combined result. For instance, if you have two tables, Vendor
, and Customer
, and you want a combined list of names, you can easily do so using:
SELECT ‘Vendor’, V.Name
FROM Vendor V
UNION
SELECT ‘Customer’, C.Name
FROM Customer C
ORDER BY Name
Note the ORDER BY
clause applies to the combined result.
INTERSECT Operator
Use an intersect operator to return rows that are in common between two tables; it returns unique rows from both the left and right query. This query is useful when you want to find results that are in common between two queries. Continuing with Vendor
s, and Customer
s, suppose you want to find vendor
s that are also customer
s. You can do so easily using:
SELECT V.Name
FROM Vendor V
INTERSECT
SELECT C.Name
FROM Customer C
ORDER BY Name
You can also use an INNER JOIN
to answer the same question:
SELECT Distinct V.Name
FROM Vendor V
INNER JOIN Customer C
ON V.Name = C.Name
ORDER BY V.Name
returns the same results.
You’ll find there is usually more than one way to solve a problem in SQL.
EXCEPT Operator
Use the EXCEPT
Operator to return only rows found in the left query. It returns unique rows from the left query that aren’t in the right query’s results. This query is useful when you’re looking to find rows that are in one set but not another. For example, to create a list of all vendors that are not customers, you could write:
SELECT V.Name
FROM Vendor V
EXCEPT
SELECT C.Name
FROM Customer C
ORDER BY Name
Like INTERSECTION
, EXCEPT
has an equivalent SQL statement. In this case, we can use an OUTER JOIN
to construct its equivalent:
SELECT Distinct V.Name
FROM Vendor V
LEFT OUTER JOIN Customer C
ON V.Name = C.Name
WHERE C.Name is NULL
ORDER BY V.Name
Tricky Stuff
You can build complicated queries using these operators. In fact, there’s nothing stopping you from combining one or more of these operators into a super query. When this is done, be sure to use parenthesis “()
” to control which operators are evaluated first.
It may not be apparent to you or another SQL reader that...
SELECT A FROM TA
INTERSECT
SELECT B FROM TB
EXCEPT
SELECT C FROM TB
UNION
SELECT D FROM TD
...evaluates as:
((SELECT A FROM TA
INTERSECT
SELECT B FROM TB)
EXCEPT
SELECT C FROM TC)
UNION
SELECT D FROM TD
When there are no parenthesis, the order of evaluation is:
INTERSECT
EXCEPT
and UNION
are evaluated Left to Right
Can you remember this?
My recommendation is just use parenthesis and make it clear. Tricky is kewl, but you’ll get burned down the road when you misread your own code – trust me on this one…
Out of the three queries, the UNION
operator is irreplaceable. There is no other way to combine results from two queries into a single result without using UNION
.
On the other hand, as you saw earlier, both EXCEPT
and INTERSECT
’s results can be reproduced using OUTER
and INNER JOINS
respectively. In fact, you’ll find that the JOIN
version of the queries runs more efficiently than EXCEPT
and INTERSECT
do and is more versatile as you can include fields from the left table that aren’t in the right.
For instance:
SELECT V.Name, V.Address
FROM Vendor V
EXCEPT
SELECT C.Name
FROM Customer C
ORDER BY Name
isn’t valid, since the number of columns in both queries don’t match, whereas...
SELECT Distinct V.Name, V.Address
FROM Vendor V
LEFT OUTER JOIN Customer C
ON V.Name = C.Name
WHERE C.Name is NULL
ORDER BY V.Name
...is valid.