Intersect operator in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 89 of signal silver
tutorial in this video we'll discuss the
intersect operator in sequel server and
the difference between intersect and in
a join so what is this intersect
operator do this operator retrieves the
common records from both the left and
the right query of the intersect
operator let's understand this with an
example here we've got two tables table
a and table B notice both the tables
have got the same number and type of
columns if you look at the data employee
records 2 & 3 are present in both the
tables now what we want to do is write a
query that's going to retrieve the
common rules that are present in both
the tables we can achieve this two ways
we can achieve this either by using the
intersect operator or by using inner
join let's look at both the ways let's
flip to sequel server management studio
so this query is going to retrieve all
the rules from table a now I'm going to
use intersect operator and retrieve ID
name gender from table B so if we
execute this intersect query we should
get all the rules that are common
between both the tables now if you look
at these two tables the common rows or
with IDs 2 and 3 so we get those in our
result set right here now let's see how
to achieve exactly the same thing using
in a join so what we want to do now is
select ID name gender from table a in a
join with table B and we have to specify
the join condition and the common column
between both the tables is ID so on
table a dot ID equals table B dot ID now
if we execute this inner join query we
are going to get an error that's because
in both the tables we have got the same
column names ID name gender so that's
why we get this ambiguous column name
error three times so we will have to
specify the name of the table from which
we want these columns we can either use
table a or table B but I'm going to use
table A for this example so they've laid
out ID table a dot name and table a door
gender now let's execute these two
queries together notice we can't exactly
the same result now the obvious question
that comes to our mind at this point is
then what's the difference between inner
join and intersect operators we'll
discuss those differences in just a bit
but before that let's discuss a few more
points about this intersect operator
this operator is introduced in sequence
over two thousand five just like the
except operator for intersect operator
to work the number and the order of the
columns must be the same in both the
queries the data types must be same or
at least compatible all right now let's
look at the differences between
intersect and a join intersect filters
duplicates and returns only distinct
rules that are common between the left
and right query whereas inner join does
not filter the duplicates let's
understand this difference with an
example now I'm going to insert this
record into table a so the record is
going to have an ID value of two named
Mary gender female now if we retrieve
data from table a we should have Mary's
record repeated twice notice ID name and
gender are the same for record two and
record four okay so now let's execute
our intersect query notice that we only
get the unique rows the duplicates are
filtered out we only get once this Mary
record but if you look at inner join we
are going to get Mary record twice so
that's one of the primary differences
between intersect and inner join to make
this inner join behave like the
intersect operator use the distinct
operator so now notice if we use
distinct operator this is only going to
retrieve the distinct rows so in this
case the inner join is going to behave
like intersect operator notice we get
exactly the same result now the other
difference is inner join treats two
nulls as two different values so if you
are joining two tables based on a
nullable column and if both tables have
nulls in that joining column then inner
join will not include those rows in the
result set
whereas intersect treats
as a same value and it returns all
matching rows let's look at this in
action so to understand this difference
I'm going to insert a row with null
value for ID column into table a and
similarly into table B as well so let's
execute these to insert queries and
let's select the data that we have at
the moment in table a and table B so
there is one novel column you know for
one of the rows and if you look at name
and gender columns the data is same okay
now let's see what's going to happen
when we execute the intersect query so
when we execute the intersect query
notice that we get that null draw okay
whereas when we execute the inner join
we will not get that natural okay so
that's another difference between inner
join and intersect queries thank you for
listening and have a great day