I am new to DBMS and SQL. While practicing some basic queries i had come across this doubt.
Suppose this query’s result is null:select * from table2 b where b.age>50; i.e. there is no age in table2 greater than 50.
Why does a query like select * from table1 a where a.age> all(select b.age from table2 b where b.age>50); select all the tuples in table1
Whereas a query: select * from table1 a where a.age> any(select b.age from table2 b where b.age>50); selects 0 tuples.
What is the logic behind these (probably from the point of view of the SQL interpreter)?
Advertisement
Answer
This is the documented behaviour.
For ANY (or SOME), with emphasis added:
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.
Evaluates to FALSE if the query returns no rows.
For ALL:
Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.
Evaluates to TRUE if the query returns no rows.
As the subquery gets no rows, it is therefore expected that the ANY version evaluates to false, since
select * from table1 a where a.age> all(select b.age from table2 b where b.age>50);
evaluates as (ignoring that you can’t explicitly have true/false):
select * from table1 a where false; -- or more legally: where 1=0
and so returns no rows;
But the the ALL condition evaluates to true, since
select * from table1 a where true; -- or more legally: where 1=1
and so returns all rows.