MySQL子查询

所谓子查询就是一个 SQL 语句中包含另一个 select 语句，即查询结果作为另一个语句的操作数。

示例表：

MariaDB [mydb]> select * from t1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    3 |
|    4 |    5 |    6 |
+------+------+------+
2 rows in set (0.00 sec)

MariaDB [mydb]> select * from t2;
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|   10 |   20 |   40 |
|   40 |   50 |   70 |
|    1 |    2 |   40 |
|    1 |    2 |   80 |
+------+------+------+
4 rows in set (0.00 sec)

1. 子查询作为标量操作数

MariaDB [mydb]> select * from `t2` where `c1`=(select `c1` from `t1` where `c2`=2);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|    1 |    2 |   40 |
|    1 |    2 |   80 |
+------+------+------+
2 rows in set (0.00 sec)

2. 子查询与 in、any、all

语法：

operand IN (subquery)
operand comparison_operator ANY (subquery)
operand comparison_operator ALL (subquery)

comparison_operator: =  >  <  >=  <=  <>  !=

in：只要操作数存在于集合中即可

MariaDB [mydb]> select * from `t2` where `c1` in (select `c1`*10 from `t1`);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|   10 |   20 |   40 |
|   40 |   50 |   70 |
+------+------+------+
2 rows in set (0.00 sec)

any：与集合中的元素比较，只要任意一个满足条件即可

MariaDB [mydb]> select * from `t2` where `c1` = any (select `c1` from `t1`);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|    1 |    2 |   40 |
|    1 |    2 |   80 |
+------+------+------+
2 rows in set (0.00 sec)

all：与集合中的元素进行比较，需要所有条件都成立才可

MariaDB [mydb]> select * from `t2` where `c1` > all (select `c1` from `t1`);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|   10 |   20 |   40 |
|   40 |   50 |   70 |
+------+------+------+
2 rows in set (0.01 sec)

3. 行子查询

行子查询结果返回的是行，可以包含多列。

MariaDB [mydb]> select * from `t2` where (`c1`, `c2`) = (select `c1`, `c2` from `t1` where `c3`=3);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|    1 |    2 |   40 |
|    1 |    2 |   80 |
+------+------+------+
2 rows in set (0.00 sec)

MariaDB [mydb]> select * from `t2` where (`c1`, `c2`) in (select `c1`*10, `c2`*10 from `t1`);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|   10 |   20 |   40 |
|   40 |   50 |   70 |
+------+------+------+
2 rows in set (0.00 sec)

4. 子查询与 exists、not exists

只要子查询不返回空结果集，则 exists 为 TRUE，not exists 为 FALSE。

MariaDB [mydb]> select * from `t2` where exists (select * from `t1` where `t1`.`c1`=`t2`.`c1`);
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|    1 |    2 |   40 |
|    1 |    2 |   80 |
+------+------+------+
2 rows in set (0.00 sec)

5. 派生表

就是将查询结果作为一张表。

MariaDB [mydb]> select `cc1`, `cc2` from (select `c1`*10 as `cc1`, `c2`*10 as `cc2` from `t1`) as `tt1`;
+------+------+
| cc1  | cc2  |
+------+------+
|   10 |   20 |
|   40 |   50 |
+------+------+
2 rows in set (0.01 sec)