Difference between union intersect and except in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 90 of sequel server
tutorial in this video we'll discuss the
difference between Union intersect and
accept operators in sequel server with a
few examples the diagram here explains
the difference is much better not as we
have got two circles here the circle on
the Left represents the left query
result set whereas the circle on the
right represents the right query result
set so if we apply a union operator
between these two queries the Union
operator is going to return the unique
rows from both the left and the right
query the duplicate rows will not be
included in the result set those
duplicate rows will be remote whereas
the Union all operator is going to
include the duplicates as well intersect
operator is going to retrieve the common
unique rows from both the left and the
right query whereas the except operator
is going to return as the unique rows
from the left query that are not there
in the right queries results let's
understand these differences with a few
examples so here we've got two tables
table a and they will be notice both the
tables have got same number and type of
columns if you look at the data notice
employee records with ID two and three
are present in both the tables and if
you look at the employee record with ID
three its duplicated in table a now
let's see what result we are going to
get when we apply these different sequel
server operators let's slip over to
sequel management studio so when we
execute this select query we are going
to get all the rows from table a now
let's union that with ID name gender
from table B so when we execute this
query notice that we get all the rows
from both the tables and we get only the
unique rows there are no duplicates
present now if we apply Union all
operator notice that we get the
duplicates as well now let's see what's
going to happen when we apply the
intersect operator when we apply the
intersect operator it should return us
all the common unique rows from both the
left and the right query so instead of
Union all let's use intersect and notice
we get the common rows and records with
ID two and three are present
both the tables and look at that result
set the duplicates are not present now
if we use the except operator this
should give us all the rules that are
present in table a but not in table B so
when we execute this notice that we will
get only the record with ID equals 1
because that's the only row that's
present in table a but not in table B
now for some reason if we want throws
the present in table B but not in table
a then we can achieve very easily by
reversing these queries so I want to
select ID name gender from table B
except ID name gender from table a so
when we execute this query now we are
going to get the row that is present in
table B but not in table a so if you
look at this row with ID 4 that's the
only row that's present in table B but
not in table a so for all these
operators to work there are two
conditions both the left and right query
should be meeting and those two
conditions are the order and the number
of columns in both the queries should be
same and the other condition is the data
types must be same or at least
compatible now let's see what's going to
happen if the number of columns are
different between the left and right
query so when we execute this query
notice that we get an error saying all
queries combined using a union intersect
or accept operators must have an equal
number of expressions in their target
list
thank you for listening and have a great
day