Comparisons Using Subqueries

Description

The most common use of a subquery is in the form:

  1. non_subquery_operand comparison_operator (subquery)

Where comparison_operator is one of these operators:

  1. = > < >= <= <> != <=>

Syntax

  1. > SELECT column_name(s) FROM table_name WHERE 'a' = (SELECT column1 FROM t1)

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. mysql> select * from t3 where a = (select b from t2);
  10. ERROR 1105 (HY000): scalar subquery returns more than 1 row
  11. mysql> select * from t3 where a = (select distinct b from t2);
  12. +------+
  13. | a |
  14. +------+
  15. | 7 |
  16. +------+
  17. 1 rows in set (0.01 sec)
  18. mysql> select a,b from t4 where a > ( select a ,b from t2 where a>1);
  19. ERROR 1105 (HY000): Internal error: Unknow type TUPLE

Constraints

MatrixOne does not support selecting multiple columns for the subquery.