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:
- 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, 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.
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 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.
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 is no parenthesis, the order of evaluation is:
- INTERSECT
- 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.
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 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
Is valid.
goooooooood
Thanks! I’m sure glad you liked the article.
it is absolutely good… have a nice day everyday to who ever did this report
Thanks for the compliment, you made my day!
I try really hard to make SQL understandable for everyone. I’m glad you liked the article. If there are other topics you would like me to cover, please let me know.
Kris.
Thanks Kris – I’m using this as a guide and set of handouts to provide to a class I am giving (for free!) to my co-workers once a week who have no training in SQL but are now responsible for writing reports for our hospital. I have 20+ years of writing SQL code, but this will be my first time teaching. You’ve saved me a TON of time and effort in organization and easy-to-understand explanation for those new to the field (sorry for the small pun :) ).
Hi John,
I’m glad you’re finding the site useful. It makes my day! Your students are the EXACT people I’m trying to reach.
As you get into some more of the complicated areas, such as joins and subqueries, you may want to check out my SQL201 course. It is pretty inexpensive and offers online video as well as training guides.
You can find more information about the class at here!
Thanks Kris,
The image at the top of this post is pure gold.
Very nice article, enjoyed that one, however – you’re saying that UNION is irreplaceable and I think this is not true – correct me if I’m mistaken but you can achieve exactly the same result using FULL OUTER JOIN. I’d rather say UNION ALL is irreplaceable, given that full outer join will not show duplicate results.
True, but without getting the lawyers involved, the spirit is that without UNION you can’t have UNION ALL, and for the sake of simplicity, in understanding the article, and writing, UNION and its various modifiers, such as ALL, are the ones which can’t be replaced…
Good to hear from you!
Every day, you post something new!
You are the best teacher!
Thanks
I have checked your site and i have found some duplicate content, that’s
why you don’t rank high in google, but there is
a tool that can help you to create 100% unique content, search for: Boorfe’s tips unlimited content
Is there any situation in which the inner/outer join are not interchangeable? For Example I want to find the maker that produces PCs but not Laptops. This was the code for it.
SELECT maker FROM Product
WHERE type = ‘PC’
EXCEPT
SELECT maker FROM Product
WHERE type = ‘Laptop’
Could you use the inner join the same way for the same table? If so how would I approach it using the inner join and not except clause?
Thanks, very useful!
Can you please share the query of
– Only A and Only B means (A union B) – (A interaction B )
If you need the ‘math’ version you can write it in many different ways, I would suggest
(Select * from A except Select * from B)
union ALL
(Select * from B except Select * from A)
As this gives two reduced sets, and the union ALL do not need to compare (as we know they are disjunct due to the excepts).
If you are only interested in actually comparing a few fields, it can be more efficient to do a FULL OUTER JOIN, and then filter those where a key value is NULL in either of the sets
But you would then need expressions like Isnull(A.fie1,B.Fie1) as Fie1 in the select part, so harder to read, and more work to write.
…Most likely you are more likely to want something like
(Select ‘A’ Src, * from A except Select * from B)
union ALL
(Select ‘B’ Src, * from B except Select * from A)
To be able to distinguish from where it came.
Moderator please correct, obviously:
(Select ‘A’ Src, * from A except Select ‘A’, * from B)
union ALL
(Select ‘B’ Src, * from B except Select ‘B’, * from A)
you make a general comment the “outer join” is more efficient than “except” for SQL Server 2008 and above I disagree indexes available max a tremendous difference in the system join types used (Nested Loops joins, Merge joins, Hash joins)
Tebby Wafer
Where speed is of the essence, and you can not use a rule of thumb, my recommendation is to always compare query plans.
Very informative. Also is ‘kewl’ some kind of programming acronym only the seasoned programmers know?
Thank you Kris, this is very helpful, it was obvious to me that intersect = inner join, but I don’t normally use outer joins so didn’t realise it could work instead of except and even better.
The order of evaluation when there is no parenthesis, is:
INTERSECT
EXCEPT and UNION are evaluated Left to Right
This is crucial to remember.
Thanks for the guide.