edited by
2,012 views
5 votes
5 votes
create table stud(id int, grade varchar(20));

insert into stud values(1, 'A');
insert into stud values(2, 'A');
insert into stud values(3, 'B');
insert into stud values(4, 'B');

// Working of ALL : >=ALL, =ALL, >ALL : All the combinations Returning all rows

Select id from stud s WHERE s.grade =ALL (Select grade from stud e WHERE
e.id = 5);

// Working of ANY : >ANY, =ANY, >=ANY : All the combinations Returning no rows

Select id from stud s WHERE s.grade >ANY (Select grade from stud e WHERE e.id = 5);

Why is ANY and ALL behaving differently? Following are the queries -

I think the inner subquery rerturns NOTHING and NOT a null. Is that an empty set?

How is ANY and ALL deciding their output with respect to empty set?

edited by

2 Answers

4 votes
4 votes
Irrespective of operand, op ALL(empty Set) is always TRUE and op ANY(empty Set) is always FALSE

In the first query , All nows will be selected and in the query, no row will be selected.
0 votes
0 votes
ALL (1 st condition and 2nd and 3 rd.. ) than ALL becomes True .

ANY(1st condition OR 2nd OR 3rd....) if not any than return false.

Related questions

7 votes
7 votes
1 answer
1
1 votes
1 votes
2 answers
3
gari asked Jan 18, 2018
1,208 views
what will be the output(how many tupples)
3 votes
3 votes
2 answers
4
yg92 asked Dec 31, 2016
594 views
select studentid, studentname from student where birthyear <=ALL( select birthyear from student);Returns detail of the youngest studentReturns detail of oldest studentSho...