Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 33.8Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 197.9Kb
Man Pages (Zip) - 301.8Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

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

14.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 13.3.2, “Comparison Functions and Operators”.

NOT IN is not an alias for <> ANY, but for <> ALL. See Section 14.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
  Posted by anonymous on September 2, 2004
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2); example:
if there is a row containing (10) in table t1, the expression is true if table t2 contains (20,10) because 10 is not equal to one or more (i.e., some) of t2's values, namely the 20.

A NOT IN condition, otoh, would be false. NOT IN is always going to be at least as hard to satisfy (as <> SOME) because to be true /all/ rows have to be non-equal.
  Posted by sukasom chaiyakul on April 8, 2005
# table paper, contains the record of each research paper
CREATE TABLE paper
(
paperid INTEGER NOT NULL AUTO_INCREMENT,
title varchar(256) not null,
PRIMARY KEY(paperid)
)

# One paper can have many reviewing,
# table reviewing contains the reviewing records.
CREATE TABLE reviewing
(
reviewingid INTEGER NOT NULL AUTO_INCREMENT,
paperid INTEGER NOT NULL,
reviewerid INTEGER NOT NULL,
unique(paperid, reviewerid),
PRIMARY KEY(reviewingid),
FOREIGN KEY (paperid) REFERENCES paper (paperid),
FOREIGN KEY (reviewerid) REFERENCES reviewer (reviewerid)
);

#table score , each record contains the score of each reviewing and each questions
CREATE TABLE score
(
reviewingid integer ,
score integer,
FOREIGN KEY (reviewingid) REFERENCES reviewing (reviewingid)
);

#find all paperid that are not dont from reviewing
# Finish reviewing paper are paper that
# for each reviewing answer 9 QAs
# for paper there are three done reviewings.
# We want to find paper that are not done from reviewing.
SELECT paperid from paper
WHERE paperid not in
(
SELECT paperid FROM
(
SELECT paperid, COUNT(paperid) as numdonereviewing from
(
SELECT reviewing.paperid , COUNT(*) as numcheck from reviewing, score
where reviewing.reviewingid = score.reviewingid
GROUP by reviewing.reviewingid
) as temp1
WHERE numcheck = 9
GROUP by paperid
) AS temp2 WHERE numdonereviewing >= 3
);

  Posted by Patrick Jackson on March 3, 2007
Using IN in a DELETE subquery is a handy way to remove orphans.

Suppose you want to create foreign keys on an existing table with orphans. If you try to ALTER TABLE to create the foreign keys, get the dreaded:

error 1452 "cannot add or update a child row: foreign key constaint fails"

This is a good thing, because after implementing foreign keys you want your tables to be consistent, and not contain orphans, so you must delete them beforehand.

Given tables 'child' and 'parent' where child.parent_id is a foreign key referencing parent.id, use the following to clean up any orphans.

DELETE FROM child WHERE child.parent_id NOT IN (SELECT id FROM parent)
Sign Up Login You must be logged in to post a comment.