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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/