|
所谓子查询就是一个 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)
|