SQL Server except operator Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 87 of sequel server
tutorial in this video well discuss the
sequel server except operator with a few
examples so what does this accept
operator do this operator returns unique
rows from the left query that are not
there in the right queries results let's
understand this with an example we'll be
using these two tables table a and table
B in this demo I have already created
these tables and here is the sequel
script to create them and populate with
test data
I'll have the script available on my
blog in case you need it now let's write
a query that's going to involve both
these tables and the except operator so
what we want to do is select ID name
gender from table a so when we execute
this query as expected we are going to
get all the five rules that are present
in this table table a now along with
that query I also want to use the except
operator and we want to select ID name
gender from table B now when we execute
this query what is the result that we
expect now what is this except operator
going to do it's going to return as the
rows from the left query that are not
there in the right queries results and
if you look at these two tables these
records that is employee John and Sarah
those two records are present in both
the tables so when we execute this
accept query it should only written as
the first three rows so let's look at
that in action notice that we get the
rows from 1 to 3 so that's what the
except operator is going to do this
operator is introduced in sequel server
2005 the most important thing to keep in
mind is that for the except operator to
work the number and the order of the
columns must be the same in both the
queries and the data types must also be
either same or compatible what's going
to happen if the number and the order of
the columns is not same for example
let's remove this gender column here so
in this query we only have two columns
whereas in this query we have got three
columns and if I execute this notice
that we get an error similarly you know
in this case the number is the same but
the order is different
again when we execute this we are going
to get the same error okay so that's
very important the number and the order
of the columns and the data types must
be either same or at least compatible
and if you are from Oracle background we
can relate this except operator to minus
operator in Oracle now in this example
we have you know returned the results
that are present in table a but not in
table B now what if you want the other
way around
I want the rules that are present in
table B but not in table a if that's the
case you can reverse these queries so
select ID name gender from table B
except ID name gender from table a so
when we execute this we only get the
rules that are present in table B but
not in table a so we only get here in
this case six seven eight which are
present in table B but not in table a
now here we have seen how to use the
except operator on two different tables
is it possible to use it on a single
table absolutely you can use it on a
single table and you can even have where
Clause so for the single table example
we will use this table TBL employees
again I've already created that and
populated it the test data and here is
the sequel script to do that so what we
want to do is select name ID name gender
and salary from table TBL employees so
when we execute this query it's going to
give us all the employee rows right now
what I want to do is apply a where
clause where salary is greater than or
equal to 50,000 so what is this going to
do it's going to give us all the
employees who has a salary greater than
or equal to 50,000 now I'm going to use
accept operator and I'm actually going
to make a copy of the Select query and
then I'm going to change the where
Clause here so I'm going to say where
salary is greater than or equal to
60,000 now when we execute this we
should only get those employees whose
salary is between
50,000 and 60,000 so we are using it on
the same table the except operator now
we can also use order by class when we
use the except operator but you have to
use it after the right query that is
after the second query you can't use
order by right here let's say for
example we want to order by ID in
descending order if I say order by ID
descending here we will get an error you
have to use it after the second query
that is the right query so now when we
execute that we get the result and
notice that the result is ordered by ID
in descending order thank you for
listening and have a great day