sql query

Question

Consider the following table Mystery

Mystery

A	B
1	Null
2	4
3	5
Null	6

select count() from Mystery
where A not in( select B from Mystery)

(A) 1

(B) 0

(C) 4

(D) 3

ans is B given

please explain how??

Answer 1

This will give 0...because of null values...where will work like if it is true for that value then the row is returned or false it will not return the row with that value....on the otherhand null is a unknown value so when it is compared with true,false or unknown it return unknown itself so when there are null the not in operator cannot give the correct truth or false values...

 

on the other hand "in" will work perfectly fine

the query can be simplified like this 

select count() from Mystery 
where A not in( select B from Mystery)

this will evaluate as

select count() from Mystery 
where (1<>NUll and 1<>4 and 1<>5 and 1<>6) or (2<>NULL and 2<>4......)or (3<>Null.....) or (NULL<>NULL....)

so 1<>NUll--unknown and 1<>4--true and 1<>5--true and 1<>6--true

eventhough rest of them are true due to "and" of unknown the expression evaluates to unknown...

where will return particular row only when it is evaluated to true

Comments

please explain how query work intenally because it's confusing.

how output is zero??

---

i edited it ...have a look

---

U said that IN operator will work fine means will it give false when NULL will come in picture??

---

for in there will be or instead of and.

1=NUll or 1=4 or 1=5 or 1=6

---

wht will query rturn if it's in oprator

---

so what will be the answer in case of IN?

---

let take an example.....

..1<>NULL  ....actully this condition is unknown therefor it will result false for all condition....

means if you are checking for equal then it will think it may not be equal and if you are checking for not equal then it will think it may be equal.....means in both the cases it will result false........