12

I'm using SQL (SQL Server, PostgreSQL) over 10 years and still I'm never used ANY/SOME and ALL keywords in my production code. All situation I've encountered I could get away with IN, MAX, MIN, EXISTS, and I think it's more readable.

For example:

-- = ANY
select * from Users as U where U.ID = ANY(select P.User_ID from Payments as P);

-- IN
select * from Users as U where U.ID IN (select P.User_ID from Payments as P);

Or

-- < ANY
select * from Users as U where U.Salary < ANY(select P.Amount from Payments as P);

-- EXISTS
select * from Users as U where EXISTS (select * from Payments as P where P.Amount > U.Salary);

Using ANY/SOME and ALL:

So the question is: am I missing something? is there some situation where ANY/SOME and ALL shine over other solutions?

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • 1
    I haven't used them either in the past 13 years. – Daniel Hilgarth Jul 11 '13 at 08:04
  • I never used `EXCEPT` either. I stick to `NOT EXISTS` – joop Jul 11 '13 at 08:30
  • I couldn't agree with that, I think `EXCEPT` is useful to find differences between two tables with same schema – Roman Pekar Jul 11 '13 at 08:33
  • But tables with the same structure (within the same schema) are rare. Could be different when you allow views or table expressions, though. – joop Jul 11 '13 at 08:36
  • 1
    In Postgres, the ANY/ALL syntax is useful for searching ARRAYs, though I've never used it in vanilla SQL. [Array searching](http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-SEARCHING) – bma Jul 11 '13 at 14:41
  • @RomanPekar, `= ANY` and `IN` are alias for subqueries. So your first comparison doesn't make much sense actually, it's comparing the same thing with itself. – Pacerier May 04 '15 at 08:48
  • @Pacerier well it's not the point of the question, question is is `any`, `some` and `all` could be easily removed from the code – Roman Pekar May 04 '15 at 11:16
  • @joop Actually, I find that `EXCEPT` is an easy way to compare the results of two `SELECT` statements: `SELECT … EXCEPT SELECT …` would yield an empty set of the results are the same. – Manngo Nov 11 '17 at 10:45

3 Answers3

13

I find ANY and ALL to be very useful when you're not just testing equality or inequality. Consider

'blah' LIKE ANY (ARRAY['%lah', '%fah', '%dah']);

as used my answer to this question.

ANY, ALL and their negations can greatly simplify code that'd otherwise require non-trivial subqueries or CTEs, and they're significantly under-used in my view.

Consider that ANY will work with any operator. It's very handy with LIKE and ~, but will work with tsquery, array membership tests, hstore key tests, and more.

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

or:

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

Without ANY or ALL you'd probably have to express those as a subquery or CTE over a VALUES list with an aggregate to produce a single result. Sure, you can do that if you want, but I'll stick to ANY.

There's one real caveat here: On older Pg versions, if you're writing ANY( SELECT ... ), you're almost certainly going to be better off in performance terms with EXISTS (SELECT 1 FROM ... WHERE ...). If you're on a version where the optimizer will turn ANY (...) into a join then you don't need to worry. If in doubt, check EXPLAIN output.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 2
    +1, but what RDBMS are you using? I've tried to put your query into work in PostgreSQL and best I could get is `like any (values ('%lah'), ('%fah'), ('%dah'));` [SQL FIDDLE](http://sqlfiddle.com/#!12/ac24b/9) – Roman Pekar Jul 11 '13 at 09:14
  • 2
    It needs to be `ANY(ARRAY[...])`. But yes, this is generally quite useful. – Peter Eisentraut Jul 11 '13 at 13:23
  • @PeterEisentraut Whoops, good point. I keep on thinking it takes the same simple literal-list syntax as `IN (...)` – Craig Ringer Jul 11 '13 at 14:02
  • Interesting - could you add somemore examples or links which illustrate your second point, please? – Beryllium Jul 11 '13 at 17:50
  • In cases where ANY/ALL mean MAX/MIN what advantages have one option over the other? – skan Sep 16 '14 at 20:34
  • @skan That doesn't make sense. `min` and `max` are aggregates. `any` and `all` are completely different. – Craig Ringer Sep 16 '14 at 23:40
6

No, I've never used the ANY, ALL, or SOME keywords either, and I've never seen them used in other people's code. I assume these are vestigal syntax, like the various optional keywords that appear in some places in SQL (for example, AS).

Keep in mind that SQL was defined by a committee.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

I had tried anything but no missing anything, just different type of habit only if i use a Not condition. the exists and in will need to add not while any/some just change the operator to <>. i only use sql server and i not sure about the other software might missing something

Tan Suiwseng
  • 77
  • 9