CODEWITHSUNDEEP
sql
newprint
newprint

Reputation: 7136

Counter intuitive meaning of < ANY & >ANY logic operators in SQL

ANY intuitively means: anything that matches in the set. However, I found out it, it doesn't behave as expected in two cases:

x > ANY(...) The value must be greater than the smallest value in the list to evaluate to TRUE.
x < ANY(...) The value must be smaller than the biggest value in the list to evaluate to TRUE

(Source)

In other words, the entire result of sub-query introduced by ANY has to be compared to x, which runs counter to meaning of ANY. Why is it so, or I am just misunderstanding the semantics of <ANY & >ANY operators ?

Upvotes: 1

Views: 86

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

x > ANY(...) The value must be greater than the smallest value in the list to evaluate to TRUE

This is stating a consequence. It may take some thinking about. x may be greater than every value in the list, and it will still return TRUE. The opposite may be easier to understand:

x > ANY(...) will be FALSE if the value is less than or equal to the smallest value in the list, because it must also be less than or equal to all other members of the list

Or yet another way:

x > ANY(...) will be true if there is at least one value in the list, y say, that x is greater than. If there are a set of such y values, the smallest value in the list must logically be one member of that set.

Upvotes: 1

Pramod S. Nikam
Pramod S. Nikam

Reputation: 4539

x > ANY(...) The value must be greater than the smallest value in the list to evaluate to TRUE. x < ANY (...) The value must be smaller than the biggest value in the list to evaluate to TRUE

Both of the above statements assert the Algorithm that oracle might using for comparison. for example.

select * from X where id > Any ( 1 , 2 , 3 , 4 , 5 )

in above code can be drill down as 

id > 1  OR  id > 2  OR id > 3 and so on..

so only id > 1 will assert it true. (Algo Optimization) Same way they did it for x < ANY (...)

Upvotes: 2

Related Questions