What’s the difference between SOME, IN and ANY?

Question

What is the difference of SOME, IN and ANY in Mysql and when to use them?

They have a bad performance if used often in the same SELECT?

Answer 1

In accordance with official Mysql documentation

ANY

"returns TRUE if the comparison is true for whichever 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 row in the table t1with the value 10. The expression is true if the table t2 contain (21, 14, 7)for 10 > 7.

IN

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

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

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

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 equal:

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-> Like ANY, but limited to expressions of equality (IN <-> = ANY). In addition to a subquery you can also receive a list of values.

Answer 2

ANY and SOME are synonyms. Simply correlating, both work as a command EXISTS.

That:

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

It’s the same as that:

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

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

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