ANY and SOME are synonymous. By simply correlating, both function as a EXISTS command.

That:

SELECT  *
FROM    mytable
WHERE   x <= ANY
        (
        SELECT  y
        FROM    othertable
        )

It's the same as this:

SELECT  *
FROM    mytable m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   m.x <= o.y
        )

With an equality condition in a Not-nullable field, it is similar to IN

As for performace, these commands are considered the slowest within SQL, so avoid using many in a query.

As official MySQL documentation

ANY

  

"returns TRUE if the comparison is true for any of the values in the column that the subquery returns." For example:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

     

Suppose there is a line in the t1 table with the value 10 . The expression is true if the t2 table contains (21, 14, 7) because 10 > 7 .

IN

  

When used in a subquery, the word IN is a nickname for = ANY . So, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

     

IN and = ANY are not synonymous when used with a list of expressions. IN can receive a list of expressions, but = ANY can not.

mysql> SELECT 2 IN (0,3,5,7);
         -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
         -> 1

SOME

  

The word SOME is a nickname for ANY . So, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

TL; DR

• ANY / SOME - > Equal expressions. For any condition, returns TRUE if there is any value in the subquery that satisfies it.
• IN - > As ANY , but limited to equality expressions ( IN < - > = ANY ). In addition to a subquery you can also get a list of values.