Except Operation in SQL

Except Operation in SQL

You can Simply think of Except as a Set Operation where from two tables table1 and table 2 you are showing only those records from table1 that are not present in table2.

its like Table1-Table2

To Explain in a more better way we will create two tables

Two table Employee1 and Employee2

create table Employee1

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee1 values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee1

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

create table Employee2

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000'),('arun','9500'),('akash','10500')

select * from Employee2

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

6 arun        9000

7 akash      10500

Now we want output like records present in Employee2  which are not in Employee1

i.e

6 arun        9000

7 akash      10500

For this we will use Except Operation 

Select * from Employee2

except

select * from Employee1

Output

6 arun        9000

7 akash      10500

you get those records from employee2 which were not present in Employee1

The EXCEPT set operation logically first eliminates duplicate rows from the two input tables then returns only rows that appear in the first tables not the second.

Note: The other alternative of Except are Outerjoins and Not Exists predicate but the advantage of Except Operation over their alternative is that it consider two Nulls rows as equal like Intersect Operation while there alternates don't.