Subqueries using the ANY keyword will return
true if the comparison returns
true for at least one row returned by the subquery.
The required syntax for an
SOME quantified comparison is:
scalar_expression comparison_operator ANY <Table subquery>
scalar_expression comparison_operator SOME <Table subquery>
scalar_expressionmay be any expression that evaluates to a single value.
comparison_operatormay be any one of
TRUEif the comparison operator returns
TRUEfor at least one row returned by the Table subquery.
FALSEif the comparison operator returns
FALSEfor all rows returned by the Table subquery, or Table subquery has zero rows.
NULLif the comparison operator returns
NULLfor at least one row returned by the Table subquery and doesn't returns
TRUEfor any of them, or if scalar_expression returns
SOME is a synmonym for
IN is a synonym for
CREATE TABLE sq1 (num TINYINT); CREATE TABLE sq2 (num2 TINYINT); INSERT INTO sq1 VALUES(100); INSERT INTO sq2 VALUES(40),(50),(120); SELECT * FROM sq1 WHERE num > ANY (SELECT * FROM sq2); +------+ | num | +------+ | 100 | +------+
100 is greater than two of the three values, and so the expression evaluates as true.
SOME is a synonym for ANY:
SELECT * FROM sq1 WHERE num < SOME (SELECT * FROM sq2); +------+ | num | +------+ | 100 | +------+
IN is a synonym for
= ANY, and here there are no matches, so no results are returned:
SELECT * FROM sq1 WHERE num IN (SELECT * FROM sq2); Empty set (0.00 sec)
INSERT INTO sq2 VALUES(100); Query OK, 1 row affected (0.05 sec) SELECT * FROM sq1 WHERE num <> ANY (SELECT * FROM sq2); +------+ | num | +------+ | 100 | +------+
Reading this query, the results may be counter-intuitive. It may seem to read as "SELECT * FROM sq1 WHERE num does not match any results in sq2. Since it does match 100, it could seem that the results are incorrect. However, the query returns a result if the match does not match any of sq2. Since
100 already does not match
40, the expression evaluates to true immediately, regardless of the 100's matching. It may be more easily readable to use SOME in a case such as this:
SELECT * FROM sq1 WHERE num <> SOME (SELECT * FROM sq2); +------+ | num | +------+ | 100 | +------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.