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

this is part 88 of signal silver
tutorial in this video we'll discuss the
difference between accept and Norton
operators in sequel server this is
continuation to part 87 so please watch
for 87 before proceeding in a previous
video session we discuss that the except
operator returns all the rules from the
left query that are not there in the
right queries results not an operator
also does exactly the same thing 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 the employee rows 2 & 3 are
present in both the tables now what we
want to do is write a query that's going
to return us only the rows that are
present in table a but not in table B to
achieve this we can either use the
except operator or not in operator let's
look at that in action so when we
execute the Select query we are going to
get all the rows and columns from table
a now let's use the except operator and
then select ID name gender from table B
so if we execute this query we should
get only the rows that are present in
table a but not in table B so notice we
just get that one row which is present
only in table a but not in table B now
we can achieve this exact same thing
using not in operator as well let's see
how to do it using the not in operator
so we want to select ID name gender from
table a where ID not n select
I D from table B so let's execute these
two queries together notice we get
exactly the same result for both the
queries so the obvious next question
that comes to our mind is well then
what's the difference between accept and
Naughton operators there are two key
differences except operator filters
duplicates and returns only distinct
rows from the left query that are not
there in the right queries results
whereas not him does not filter the
duplicates let's understand this
difference with an example at the moment
if you look at the data that we have got
in table a we don't have any duplicate
rows now what I'm going to do is insert
another record for mark so we will have
the same ID name and gender so let's
execute this insert query right here now
if we select what we have in table you
notice that mark record is repeated
twice now when we execute this accept
query notice we get only one row for
mark
that means the duplicates are filtered
whereas when we execute this not inquiry
notice that we get the duplicates so the
primary difference is that except
filters the duplicates whereas not in
will not filter the duplicates another
difference is that except operator
expects the same number of columns in
both the queries whereas not in compares
a single column from the outer query
with a single column from the sub query
so if you look at the except operator
here notice we have got to select
queries you know on the left side of the
except operator and on the right side of
the except operator and if you look at
the number of columns they are exactly
the same now what's going to happen if
the number changes so now one query has
got two columns and the other query has
got three columns when we execute that
we get an error saying all queries
combined using a Union intersector
except operator must have an equal
number of expressions in the target list
whereas if you look at the Norton
operator it compares a single column
from the outer query with
single column from the sub-query so if
you look at this not in query here so we
are comparing ID with ID so there's only
one column in the outer query that we
are comparing with one column in the sub
query now what do you think is going to
happen if this sub query is going to
return maybe two columns so when we
execute this again we are going to get
an error and if you look at the error it
says only one expression can be
specified in the Select list when the
sub query is not introduced with XS so
those are the key differences between
accept and not in operators in sequel
server thank you for listening and have
a great day