Difference Between Except and Not In and Not Exist

These are Sql keywords we usually used to filter data between two tables mostly.

Let' s try to understated differences between these keywords with the help of a simple example.
 we have two tables

 

Table1                                                      
 

id           name2                                                                                             

2            nikita

3            dinesh

4            jeetu

5            Andrew

NULL     harshit

NULL     naveen

7            ravikant

           

Table 2
 

name2    name3

jeetu        meetu

lovi          nughty

nikita       rghty

NULL       fdsa

faisal          rete

dhamrpal       twer

naveen    kishan

harshit     NULL

 

Not In
 

Now executes following command
 

select name2 from Table1 where name2 not in(select name2 from Table2)
 

What should be the result of above query?
 

Answer: Blank(no data as output)

So we should use following query
 

Select name2 from Table1 where name2 not in(select name2 from Table2 where name2 is not null)

 

Result:
 

name2
 

dinesh
 

Andrew
 

 ravikant
 

Except
 

Same result we get from except:
 

select name2 from Table2

EXCEPT

select name2 from Table3

Result:

name2

dinesh
 

Andrew
 

ravikant
 

Note : no of parameters in both select state should be same but not necessary in Not in
 

Not Exists
 

• Not Exist is same as Not in except that we need to define condition in second select statement

 

select name2 from Table1 where

not exists

(select name2 from Table2 where Table1.name2=Table2.name2)

Result:

name2
 

dinesh
 

Andrew
 

 ravikant