+1 vote
33 views

Consider the following table Mystery

 A B 1 null 2 4 3 5 null 6

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

What is the output of this query?

(A) 1

(B) 0

(C) 4

(D) 3

edited | 33 views

It will give single tuple having value $0$.

Reasoning:

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


Inner query evaluates to $S: (null, 4, 5, 6)$

Outer query check for each tuple $t$ in table whether there exists any value $b \in S$ such that $t.A = b$. If that’s the case comparison returns false.

Now point here is that every $t.A$ is compared to $null$ exactly once and  that comparison returns $null$. So that $t$ will not counted into result.

Likewise every tuple fails to counted into result and we get count $0$.

by (1.5k points)
0
IN ( null,a,b,c) returns null
NOT IN( null, a, b, c) returns null ??
0
 A B 1 1 2 2 3 null null 3

select count() from Mystery
where A IN( select B from Mystery)

What will be the output now?

+1
The main logic is “ NULL are not comparable values”

so it should be 3 (for the comment question)
+1