ANY (synonym: SOME) is a comparison predicate defined by the SQL standard. It works with =, <>, <, >, <=, or >= to test whether the comparison holds for at least one row produced by a subquery. The predicate evaluates the scalar on the left-hand side against every value yielded by the subquery on the right. If at least one comparison is TRUE, the overall predicate is TRUE; if none are TRUE but at least one is NULL, the result is UNKNOWN; if all are FALSE, the result is FALSE.Unlike EXISTS, which only checks for the presence of rows, ANY checks the result of a comparison. ANY differs from ALL, which requires the comparison to be TRUE for every row. When used with the = operator, "x = ANY(subquery)" is equivalent to "x IN (subquery)".Performance depends on how efficiently the database can execute the subquery and apply the comparison. Indexes on columns referenced in the subquery can greatly improve execution time. Be aware that NULLs inside the subquery can propagate UNKNOWN results, so use COALESCE or WHERE clauses to filter them out when needed.
SQL-92 Standard
IN is shorthand for = ANY. Use IN for equality checks and ANY for other comparisons like < or >.
ANY is not a join operation but a predicate. However, it can replace many semi-join patterns by eliminating explicit joins.
If all comparisons evaluate to NULL or FALSE, the result is UNKNOWN and the row is filtered out unless you explicitly test for UNKNOWN.
Yes. SOME is an ANSI-SQL synonym for ANY.