Difference between union intersect and except in sql server

we will discuss the difference between union intersect and except in sql server with examples.

The following diagram explains the difference graphically

UNION operator returns all the unique rows from both the left and the right query. UNION ALL included the duplicates as well.

INTERSECT operator retrieves the common unique rows from both the left and the right query.

EXCEPT operator returns unique rows from the left query that aren’t in the right query’s results. 

Let us understand these differences with examples. We will use the following 2 tables for the examples.

SQL Script to create the tables

Create Table TableA

(

    Id int,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Insert into TableA values (1, 'Mark', 'Male')

Insert into TableA values (2, 'Mary', 'Female')

Insert into TableA values (3, 'Steve', 'Male')

Insert into TableA values (3, 'Steve', 'Male')

Go

Create Table TableB

(

    Id int primary key,

    Name nvarchar(50),

    Gender nvarchar(10)

)

Go

Insert into TableB values (2, 'Mary', 'Female')

Insert into TableB values (3, 'Steve', 'Male')

Insert into TableB values (4, 'John', 'Male')

Go

UNION operator returns all the unique rows from both the queries. Notice the duplicates are removed.

Select Id, Name, Gender from TableA

UNION

Select Id, Name, Gender from TableB

Result :


UNION ALL operator returns all the rows from both the queries, including the duplicates.

Select Id, Name, Gender from TableA

UNION ALL

Select Id, Name, Gender from TableB

Result :


INTERSECT operator retrieves the common unique rows from both the left and the right query. Notice the duplicates are removed.

Select Id, Name, Gender from TableA

INTERSECT

Select Id, Name, Gender from TableB

Result : 


EXCEPT operator returns unique rows from the left query that aren’t in the right query’s results.

Select Id, Name, Gender from TableA

EXCEPT

Select Id, Name, Gender from TableB

Result :


If you wnat the rows that are present in Table B but not in Table A, reverse the queries.

Select Id, Name, Gender from TableB

EXCEPT

Select Id, Name, Gender from TableA

Result :


For all these 3 operators to work the following 2 conditions must be met

• The number and the order of the columns must be same in both the queries
• The data types must be same or at least compatible

For example, if the number of columns are different, you will get the following error
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.