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