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.
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.
The Union operator returns rows from both tables. If used by itself, the 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.
Use an intersect operator to returns rows that are in common between two tables; it returns unique rows from both the left and right queries. 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.
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
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
((SELECT A FROM TA INTERSECT SELECT B FROM TB) EXCEPT SELECT C FROM TC) UNION SELECT D FROM TD
When there is no parenthesis, the order of evaluation is:
- EXCEPT and UNION are evaluated Left to Right
Can you remember this?
My recommendation is to 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.
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 doesn’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