mysql - SQL: do we need ANY/SOME and ALL keywords? -

i'm using sql (sql server, postgresql) on 10 years , still i'm never used any/some , all keywords in production code. situation i've encountered away in, max, min, exists, , think it's more readable.

for example:

-- = select * users u u.id = any(select p.user_id payments p);  -- in select * users u u.id in (select p.user_id payments p); 

or

-- < select * users u u.salary < any(select p.amount payments p);  -- exists select * users u exists (select * payments p p.amount > u.salary); 

using any/some , all:

• postgresql
• sql server
• mysql
• sql fiddle examples

so question is: missing something? there situation any/some , all shine on other solutions?

i find , useful when you're not testing equality or inequality. consider

'blah' (array['%lah', '%fah', '%dah']); 

as used answer question.

any, all , negations can simplify code that'd otherwise require non-trivial subqueries or ctes, , they're under-used in view.

consider any work operator. it's handy like , ~, work tsquery, array membership tests, hstore key tests, , more.

'a => 1, e => 2'::hstore ? (array['a', 'b', 'c', 'd']) 

or:

'a => 1, b => 2'::hstore ? (array['a', 'b']) 

without any or all you'd have express subquery or cte on values list aggregate produce single result. sure, can if want, i'll stick any.

there's 1 real caveat here: on older pg versions, if you're writing any( select ... ), you're going better off in performance terms exists (select 1 ... ...). if you're on version optimizer turn any (...) join don't need worry. if in doubt, check explain output.