Click here to Skip to main content
15,850,359 members
Articles / Database Development / SQL Server / SQL Server 2012

Get Ready to Learn SQL Server: 15. Learn to use Union, Intersect, and Except Clauses

Rate me:
Please Sign up or sign in to vote.
4.00/5 (2 votes)
21 Dec 2014MIT3 min read 15.8K   10   2
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.

Image 1

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:

SQL
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 Vendors, and Customers, suppose you want to find vendors that are also customers. You can do so easily using:

SQL
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:

SQL
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:

SQL
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:

SQL
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...

SQL
SELECT A FROM TA
INTERSECT
SELECT B FROM TB
EXCEPT
SELECT C FROM TB
UNION
SELECT D FROM TD

...evaluates as:

SQL
((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 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:

SQL
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...

SQL
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.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
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/

Comments and Discussions

 
QuestionThats pretty good but redundant Pin
Yasskier23-Dec-14 10:36
Yasskier23-Dec-14 10:36 
AnswerRe: Thats pretty good but redundant Pin
essentialSQL5-Feb-15 16:18
essentialSQL5-Feb-15 16:18 
Agreed! Redundant, but lots of stuff in programming are redundant...

Like you I mainly stick with joins, but I wouldn't do set operators justice if I didn't address all the keywords...
Kris - www.essentialsql.com

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.