CODEWITHSUNDEEP
mysqlsql
Karthik Chennupati
Karthik Chennupati

Reputation: 365

Comparison with empty set

I have two relations A and B

Table A

Name   Age
------------
Arun    60 
Shreya  24 
Rohit   11

Table B

Name    Age
------------
Hari     40
Rohit    20
Karthik  18

select *
from A
where A.Age > all(select B.Age from B where B.Name = 'Arun');

I know that the sub-query would return an empty set. I come to know that the all() would consider NULL value if there is an empty set. In that case, the predicate in the where clause of the outer query should evaluate to UNKNOWN, thereby eliminating all the tuples returned by from clause.

But, the query is returning all tuples from relation A as output.

Please explain how the all() function is handling the empty set.

Thanks for the help!

Upvotes: 0

Views: 617

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The ALL is doing exactly what it says. It is comparing A.age to every value returned by the query. The filter passes if A.age is larger than all the values of B.age.

There are no values, so this is true.

You are confusing an empty result set with a result set that has a row with NULL. These are different things.

Incidentally, I usually write this logic as:

select *
from A
where A.Age > (select max(B.Age) from B where B.Name = 'Arun');

In this case, the comparison is not to a set; the comparison is to a scalar value. And, the scalar value is NULL because the subquery returns no rows. So, this returns no rows in this situation. The results are the same when your subquery returns any rows.

Upvotes: 2

Related Questions