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

Constraints

MatrixOne does not support selecting multiple columns for the subquery.