Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Difference between union intersect and except in sql server

Suggested Videos
Part 87 - SQL Server except operator
Part 88 - Difference between except and not in sql server
Part 89 - Intersect operator in sql server



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



The following diagram explains the difference graphically
Difference between union intersect and except in sql server

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 :
sql server union example

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 :
sql server union all example

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 : 
sql server intersect example

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 :
sql server except example

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 :
except operator in sql server

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.

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.