0

Is the IN operator made redundant by the ANY operator? I've learned that IN and = ANY do the same thing, but any can also be used with <, >, <>, etc. so my question is are there any actual use cases for IN where =ANY wouldn't be suitable?

Dale K
  • 25,246
  • 15
  • 42
  • 71
olopaxx
  • 1
  • I think they are equivalent. – Gordon Linoff May 19 '21 at 20:56
  • These serve logically different functions, each has a specific use-case. – Stu May 19 '21 at 21:06
  • Does this answer your question? [SQL: do we need ANY/SOME and ALL keywords?](https://stackoverflow.com/questions/17588074/sql-do-we-need-any-some-and-all-keywords) To be honest, most people find `ANY` and `ALL` highly confusing – Charlieface May 19 '21 at 22:28

1 Answers1

0

For SQL Server at least they are not totally equivalent as ANY doesn't seem to accept a simple value list like IN can.

-- Works
SELECT 1 WHERE 'A' IN ('A','B')
-- Fails with Incorrect syntax near 'A'.  
SELECT 1 WHERE 'A' = ANY('A','B')
-- Works
SELECT 1 WHERE 'A'= ANY (SELECT 'A' UNION SELECT 'B')
Brian
  • 6,717
  • 2
  • 23
  • 31