10

Learn to use Union, Intersect, and Except Clauses

Learn to use Union, Intersect, and Except Clauses

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:

1. The number and order columns must be the same in both queries
2. 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 returns 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 Vendors, and Customers, suppose you want to find vendors that are also customers.  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:

1. INTERSECT
2. 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 missread 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.