Except and Intersect in SQL SERVER

Except and Intersect can be used to returns distinct values by comparing the results of two queries.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:

1. Expressions in parentheses
2. The INTERSECT operand
3. EXCEPT and UNION evaluated from left to right based on their position in the expression

------------------------------ Code Sample -----------------------------------------

Declare @tbl Table(InstructorID int,CompanyID int,btactive bit,bitprimary bit)

Insert into @tbl values (1,1,1,0),(2,1,1,1),(3,1,1,0),(4,2,1,0),(2,2,1,1),(1,3,1,0)

select * from @tbl

------------------------------ Code Sample -----------------------------------------

I have created a declare table @tbl with four columns and insert some values in it. Now i want to return all those compayIds that are active with bitPrimary = 0 but the result set must not include those companyIds which have bitPrimay= 1 value as well.

If i use simple query to return bitprimary = 0 I got following result

If we look at the results CompanyID 1 and 2 have data against bitPrimary = 0. So according to my requirement i need only to get 3 companyID in my result set. Now look at the use of EXCEPT operator. 

EXCEPT:

EXCEPT returns any distinct values from the left query that are not also found on the right query. When an EXCEPT operation is displayed by using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left anti semi join.

------------------------------ Code Sample -----------------------------------------

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 0

EXCEPT

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 1

------------------------------ Code Sample -----------------------------------------

look at the pic above. I have attached result set on top right corner which shows CompanyID 3 and this is what exactly I need to get.

We can also get similar results by using EXCEPT -- NOT EXISTS -- NOT IN. But as per many MVPs EXCEPT is best as performance then others.

Now what if i need to get all those compayIds that are active with bitPrimary = 0 but the result set must include those companyIds which have bitPrimay= 1 as well. Now i use INTERSECT

INTERSECT:

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. When an  INTERSECT operation is displayed by using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left semi join.

------------------------------ Code Sample -----------------------------------------

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 0

INTERSECT

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 1

------------------------------ Code Sample -----------------------------------------

look at the pic above. Result set shows CompanyID 1 and 2 because both have btPrimary = 0 and 1 but CompanyID 3 does not have btPrimary = 1 so that ommits.

We can also get similar results by using INTERSECT -- LEFT OUTER JOIN -- FULL OUTER JOIN.