DISQUS

Robert Vollman · 4 years ago

Interesting.

Since the query optimizer changes these to more traditional queries, I wonder why these keywords exist.

I imagine it was to some community of people to write their queries in a way that was more comfortable or understandable for them.

If so, I wonder in which database or language these key words have their origin.

What do you think?

Eddie Awad · 4 years ago

Robert,

Maybe you're right. Maybe Oracle introduced these comparison operators just because other databases have it, I do not know. I do know however that SQL Server has similar [ANY/SOME](http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_17jt.asp) and [ALL](http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_2fsc.asp) operators. I still want to prove that using ANY, for example, is slower than using >, < or = instead. I'll put it on my to-do list.

Jaime Lupa · 2 years ago

Nice post, however I believe these operators make less readable a given query.

Bala · 2 years ago

Very very descriptive and useful... Hats Off...

anguang · 2 years ago

SELECT *
FROM emp
WHERE sal >=800 AND sal >=null;
This query can return rows. Why?

SELECT *
FROM emp
WHERE sal >=null;
This query can not return rows. Why?

anguang · 2 years ago

What is the meaning of the following query?

SELECT *
FROM emp
WHERE NOT manager_id <> ALL(800,1600,null);

Eddie Awad · 2 years ago

anguang, sal >=null is meaningless. It's either sal is null or sal is not null.

sandip · 2 years ago

Really it is very nice post , It clear my confusion between any and some.

Sanjeet Kumar · 1 year ago

please tell me the querey of "

i want to details of that manager who have reported maximum no. of employees.........from emp table.

Div Shah · 1 year ago

Great Post. After 5 years I have understand real meaning and usefullness of any and all..

Hats Off

Thanks

jan · 1 year ago

Anguang,
SELECT *
FROM emp
WHERE sal >=800 AND sal >=null;
This query can return rows. Why?

Optimization, as you can see with explain plan.
My guess: 800 is sorted higher as Null, so we'll only test sal >=800.

Interesting question though.

Jan