Subqueries with EXISTS or NOT EXISTS

Description

The EXISTS operator is used to test for the existence of any record in a subquery.

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

Syntax

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

Examples

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)