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