Awesome q2a theme
+1 vote

Consider the following table Mystery

1 null
2 4
3 5
null 6

select count(\ast) 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

in Databases by (481 points)
edited by | 33 views

1 Answer

+2 votes

It will give single tuple having value $0$.


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)
IN ( null,a,b,c) returns null
NOT IN( null, a, b, c) returns null ??
1 1
2 2
3 null
null 3

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

What will be the output now?

The main logic is “ NULL are not comparable values”

so it should be 3 (for the comment question)
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
Welcome to GATE CSE Doubts, where you can ask questions and receive answers from other members of the community.
8,430 questions
2,707 answers
95,452 users