SQL: do we need ANY/SOME and ALL keywords?
ansi-sql, mysql, postgresql, sql, sql-server
Solution
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.
Problem
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`: - PostgreSQL - SQL Server - MySQL - SQL FIDDLE with some examples So the question is: am I missing something? is there some situation where `ANY/SOME` and `ALL` shine over other solutions?