Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.4Mb
PDF (A4) - 39.4Mb
PDF (RPM) - 38.7Mb
HTML Download (TGZ) - 11.1Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.8Mb
Man Pages (TGZ) - 213.4Kb
Man Pages (Zip) - 322.5Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Subqueries with ANY, IN, or SOME

13.2.10.3 Subqueries with ANY, IN, or SOME

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparison_operator is one of these operators:

=  >  <  >=  <=  <>  !=

The ANY keyword, which must follow a comparison operator, means return 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 that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).

When used with a subquery, the word IN is an alias for = ANY. Thus, 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 synonyms when used with an expression list. IN can take an expression list, but = ANY cannot. See Section 12.3.2, “Comparison Functions and Operators”.

NOT IN is not an alias for <> ANY, but for <> ALL. See Section 13.2.10.4, “Subqueries with ALL”.

The word SOME is an alias for ANY. Thus, 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);

Use of the word SOME is rare, but this example shows why it might be useful. To most people, the English phrase a is not equal to any b means there is no b which is equal to a, but that is not what is meant by the SQL syntax. The syntax means there is some b to which a is not equal. Using <> SOME instead helps ensure that everyone understands the true meaning of the query.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Daniel Krueger on December 6, 2017
I think it is important to note here that there are two different types of subqueries used int MySQL.

I found the definition of both in section 13.2.10.5 Row Subqueries (also see 3.2.10)
-- A row subquery can return only ONE ROW but multiple columns... and a
-- scalar or column subquery can return multiple rows.

So these ANY, IN or SOME I believe are to be used with scalar queries. If you have multiple columns in your subquery and multiple rows then mysql will throw Error Code 1241. Operand should contain 1 column.

Example

SELECT customerNumber, customerName
FROM customers
WHERE customerNumber NOT IN (
SELECT customerNumber, customerName #<-- 2nd operand 'customerName'throws the error
FROM orders o INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
INNER JOIN products p ON od.productCode = p.productCode
WHERE p.productName = "2001 Ferrari Enzo"
);

It is also important to point out that the subquery here shows up AFTER the WHERE clause... I don't think it is permissible to assign a subquery a table identifier when the subquery shows up after the WHERE clause.
Sign Up Login You must be logged in to post a comment.