一. 子查询
子查询就是指在一个select语句中嵌套另外一个select语句。同时子查询必须包含括号。MySQL 5.6之前,子查询的性能较差,但是从5.6开始,不存在性能差的问题。
select a from t1 where a > any(select a from t2);
1. select a from t1是外部查询(outer query)
2. (select a from t2) 是子查询
一般来说子查询嵌套与外部查询中,可以将两个或两个以上子查询嵌套
1. 子查询的使用
1.1 ANY / SOME
如果外部查询列的结果和子查询列的结果比较得到为True,则返回比较值为True的(外查询)的记录
[root@mysql.sock][dbt3_s1]> create table t1(a int);
Query OK, 0 rows affected (0.34 sec)
[root@mysql.sock][dbt3_s1]> create table t2(a int);
Query OK, 0 rows affected (0.02 sec)
[root@mysql.sock][dbt3_s1]> insert into t1 values(10),(4);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
[root@mysql.sock][dbt3_s1]> insert into t2 values(12),(13),(5);
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0
[root@mysql.sock][dbt3_s1]> select a from t1;
+------+
| a |
+------+
| 10 |
| 4 |
+------+
2 rows in set (0.00 sec)
[root@mysql.sock][dbt3_s1]> select a from t2;
+------+
| a |
+------+
| 12 |
| 13 |
| 5 |
+------+
3 rows in set (0.00 sec)
[root@mysql.sock][dbt3_s1]> select a from t1 where a > ANY(select a from t2); -- 子查询返回(12,13,5)
+------+ -- t1 a列只要大于(12,13,5)中的任一值
| a | -- 即t1.a > t2.a为True,则返回t1.a
+------+
| 10 |
+------+
1 row in set (0.07 sec)
-- 这个查询可以解释为,t1表内a列的值 大于t2表中a列的任意(any)一个值(t1.a > any(t2.a) == true),则返回t1.a的记录
ANY 关键词必须与一个 比较操作符 一起使用: = , > , < , >= , <= , <> (这个是!=的意思)
子查询中 SOME 和 ANY 是同一个意思
1.2. IN
in 是 ANY 的一种特殊情况: "in" equals "= any"
[root@mysql.sock][dbt3_s1]> insert into t1 values(5);
Query OK, 1 row affected (0.26 sec)
[root@mysql.sock][dbt3_s1]> select a from t1 where a = ANY(select a from t2);
+------+
| a |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
[root@mysql.sock][dbt3_s1]> select a from t1 where a in (select a from t2);
+------+
| a |
+------+
| 5 |
+------+
1 row in set (0.01 sec)
select a from t1 where a in (select a from t2); 是用的比较多的一种语法
1.3. ALL
如果外部查询的列的结果和子查询的列的 所有结果 比较得到为True的话,则返回比较值为True的(外查询)的记录
[root@mysql.sock][dbt3_s1]> truncate t1;
Query OK, 0 rows affected (0.20 sec)
[root@mysql.sock][dbt3_s1]> truncate table t2;
Query OK, 0 rows affected (0.06 sec)
[root@mysql.sock][dbt3_s1]> insert into t1 values(10),(4);
Query OK, 2 rows affected (0.29 sec)
Records: 2 Duplicates: 0 Warnings: 0
[root@mysql.sock][dbt3_s1]> insert into t2 values(5),(4),(3);
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0
[root@mysql.sock][dbt3_s1]> select a from t1 where a > all(select a from t2);
+------+
| a |
+------+
| 10 | -- (10 > 5, 4, 3 为True) 而(4 >5, 4, 3 为False)
+------+
1 row in set (0.00 sec)
ALL 关键词必须与一个 比较操作符 一起使用
NOT IN 是 <> ALL 的别名
2. 子查询的分类
独立子查询
不依赖外部查询而运行的子查询
[root@mysql.sock][dbt3_s1]> select a from t1 where a in (1,2,3,4,5);
+------+
| a |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
相关子查询
引用了外部查询的子查询
-- 这个例子中,子查询使用了外部的列t2.a
[root@mysql.sock][dbt3_s1]> select a from t1 where a in (select * from t2 where t1.a = t2.a);
+------+
| a |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
3. 子查询的优化
MySQL 5.6之前
在MySQL 5.6之前,优化器会把子查询重写成exists的形式