16

Learn to use Union, Intersect, and Except Clauses

Intersection, Exception, Union

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.

UnionInsersectExcept

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.

 

 

 

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA.He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame.Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Samantha pendelton says:

    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.

  • john says:

    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 :) ).

  • Liam says:

    Thanks Kris,

    The image at the top of this post is pure gold.

  • Mashchax says:

    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.

    • Kris Wenzel says:

      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…

  • Kimete says:

    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

  • Zhej says:

    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?

  • vg says:

    Thanks, very useful!

  • Anand says:

    Can you please share the query of
    – Only A and Only B means (A union B) – (A interaction B )

  • Tebby Wafer says:

    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

    • Kris Wenzel says:

      Where speed is of the essence, and you can not use a rule of thumb, my recommendation is to always compare query plans.

  • >