Subqueries with EXISTS or NOT EXISTS

语法描述

EXISTS 用于检查子查询是否至少会返回一行数据。即将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

如果子查询返回任何行，EXISTS 子查询条件为 TRUE，NOT EXISTS 子查询条件为 FALSE。

语法结构

  1. > SELECT column_name(s)
  2. FROM table_name
  3. WHERE EXISTS
  4. (SELECT column_name FROM table_name WHERE condition);

示例

  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. insert into t1 values (2);
  6. insert into t2 values (1,7),(2,7);
  7. insert into t4 values (4,8),(3,8),(5,9);
  8. insert into t3 values (6),(7),(3);
  10. mysql> select * from t3 where exists (select * from t2 where t2.b=t3.a);
  11. +------+
  12. | a    |
  13. +------+
  14. |    7 |
  15. +------+
  16. 1 row in set (0.00 sec)
  17. mysql> select * from t3 where not exists (select * from t2 where t2.b=t3.a);
  18. +------+
  19. | a    |
  20. +------+
  21. |    6 |
  22. |    3 |
  23. +------+
  24. 2 rows in set (0.00 sec)