Subqueries with ANY or SOME

语法描述

由于列子查询返回的结果集是多行一列，因此不能直接使用（=，>，<，>=，<=，<>）这些比较操作符。在列子查询中可以使用 ANY、SOME 操作符与比较操作符联合使用：

  • ANY：与比较操作符联合使用，表示与子查询返回的任何值比较为 TRUE，则返回结果为 true。
  • SOME：ANY 的别名，与 ANY 意义相同，但较少使用。

语法结构

  1. > SELECT column_name(s) FROM table_name WHERE column_name ANY (subquery);

示例

  1. create table t1 (a int);
  2. create table t2 (a int, b int);
  3. create table t3 (a int);
  4. create table t4 (a int not null, b int not null);
  5. create table t5 (a int);
  6. create table t6 (a int, b int);
  7. insert into t1 values (2);
  8. insert into t2 values (1,7),(2,7);
  9. insert into t4 values (4,8),(3,8),(5,9);
  10. insert into t5 values (null);
  11. insert into t3 values (6),(7),(3);
  12. insert into t6 values (10,7),(null,7);
  14. mysql> select * from t3 where a <> any (select b from t2);
  15. +------+
  16. | a    |
  17. +------+
  18. |    6 |
  19. |    3 |
  20. +------+
  21. 2 rows in set (0.00 sec)
  23. mysql> select * from t3 where a <> some (select b from t2);
  24. +------+
  25. | a    |
  26. +------+
  27. |    6 |
  28. |    3 |
  29. +------+
  30. 2 rows in set (0.00 sec)
  32. mysql> select * from t3 where a = some (select b from t2);
  33. +------+
  34. | a    |
  35. +------+
  36. |    7 |
  37. +------+
  38. 1 row in set (0.00 sec)
  40. mysql> select * from t3 where a = any (select b from t2);
  41. +------+
  42. | a    |
  43. +------+
  44. |    7 |
  45. +------+
  46. 1 row in set (0.00 sec)
  48. mysql> select a,b from t6 where a >  any ( select a ,b from t4 where a>3);
  49. ERROR 1105 (HY000): subquery should return 1 column