Difference between union intersect and except in sql server

difference between union and union all
difference between intersect and union
difference between intersect and except in sql server
union vs union all sql server
union vs intersect sql server
intersect vs except

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

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.

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

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

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

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

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

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.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-union-intersect-and.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-union-intersect-and_7.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists