子查询就是指在一个 SELECT 语句中嵌套另一个 SELECT 语句。同时，子查询必须包含括号。

:::info MySQL 5.6.x 版本之前，MySQL 的子查询性能较差，但是从 5.6 开始，不存在性能差的问题。 :::

  1. 1.select a from t1 where a > any(select a from t2);
  • select a from t1 是外部查询（outer query）。
  • (select a from t2) 是子查询。

一般说来，子查询嵌套于外部查询中，可以将两个或两个以上的子查询进行嵌套。

子查询的使用

ANY & SOME

如果外部查询的列的结果和子查询的列的结果比较得到为 True 的话，则返回比较值为 True 的（外查询）的记录。

  1. mysql> create table  t1 (a int);
  2. Query OK, 0 rows affected (0.15 sec)
  4. mysql> create table  t2 (a int); 
  5. Query OK, 0 rows affected (0.14 sec)
  7. mysql> insert into t1 values(10),(4);
  8. Query OK, 2 rows affected (0.02 sec)
  9. Records: 2  Duplicates: 0  Warnings: 0
  11. mysql> insert into t2 values(12),(13),(5);
  12. Query OK, 3 rows affected (0.03 sec)
  13. Records: 3  Duplicates: 0  Warnings: 0
  15. mysql> select a from t1;
  16. +------+
  17. | a    |
  18. +------+
  19. |   10 |
  20. |    4 |
  21. +------+
  22. 2 rows in set (0.00 sec)
  24. mysql> select * from t2;
  25. +------+
  26. | a    |
  27. +------+
  28. |   12 |   -- t1中10，4比12小
  29. |   13 |   -- t1中10，4比13小
  30. |    5 |   -- t1中10比5大，4比5小
  31. +------+
  32. 3 rows in set (0.00 sec)
  34. mysql> select a from t1  
  35.     -> where a > any
  36.     -> (select a from t2); -- 返回(12，13，4)
  37.                            -- t1中a列的值，只要大于(12,13,4)中任意一值
  38.                            -- 即t1.a > t2.a为True，则返回对应的t1.a
  39. +------+
  40. | a    |
  41. +------+
  42. |   10 |  -- 10比5大为True，则返回该值，4比t2中所有的a值小，为False
  43. +------+
  44. 1 row in set (0.00 sec)
  46. -- 这个查询可以解释为，t1表内a列的值大于t2表中a列的任意(any)一个值（t1.a > any(t2.a) == true）,则返回t1.a的记录
  • ANY 关键词必须与一个比较操作符一起使用： =, >, <, >=, <=, <>（这个是 != 的意思）。
  • 子查询中 SOME 和 ANY 是同一个意思。

IN

IN 是 ANY 的一种特殊情况：”in” equals “= any”。

  1. mysql> insert into t1 values(5);  -- 向t1中插入一个t2中存在的值5
  2. Query OK, 1 row affected (0.03 sec)
  4. mysql> select a from t1 where a = any(select a from t2); -- t1.a==t2.a 的只有5
  5. +------+
  6. | a    |
  7. +------+
  8. |    5 |
  9. +------+
  10. 1 row in set (0.00 sec)
  12. mysql> select a from t1 where a in (select a from t2); -- in的结果等同于 =any 的结果
  13. +------+
  14. | a    |
  15. +------+
  16. |    5 |
  17. +------+
  18. 1 row in set (0.00 sec)

ALL

如果外部查询的列的结果和子查询的列的所有结果比较得到为 True 的话，则返回比较值为 True 的（外查询）的记录。

  1. mysql> truncate t1;   -- 清空t1
  2. Query OK, 0 rows affected (0.07 sec)
  4. mysql> truncate t2;   -- 清空t2
  5. Query OK, 0 rows affected (0.10 sec)
  7. mysql> insert into t1 values(10),(4);
  8. Query OK, 2 rows affected (0.02 sec)
  9. Records: 2  Duplicates: 0  Warnings: 0
  11. mysql> insert into t2 values(5),(4),(3);  
  12. Query OK, 3 rows affected (0.03 sec)
  13. Records: 3  Duplicates: 0  Warnings: 0
  15. mysql> select a from t1 where a > all(select a from t2);
  16. +------+
  17. | a    |
  18. +------+
  19. |   10 |  -- (10 > 5, 4, 3 为 True) 而 (4 >5, 4, 3 为 False)
  20. +------+
  21. 1 row in set (0.00 sec)
  • ALL 关键词必须与一个比较操作符一起使用。
  • NOT IN 是 <> ALL 的别名。

子查询的分类

独立子查询

不依赖外部查询而运行的子查询。

  1. mysql> select a from t1 where a in (1,2,3,4,5);
  2. +------+
  3. | a    |
  4. +------+
  5. |    4 |  
  6. +------+
  7. 1 row in set (0.00 sec)

相关子查询

引用了外部查询列的子查询。

  1. -- 在这个例子中，子查询中使用到了外部的列t1.a 
  2. mysql> select a from t1 where a in (select * from t2 where t1.a = t2.a);
  3. +------+
  4. | a    |
  5. +------+
  6. |    4 |
  7. +------+
  8. 1 row in set (0.00 sec)

子查询的优化

MySQL 5.6 之前

在 MySQL 5.6 之前，优化器会把子查询重写成 exists 的形式。

  1. select a from t1 where a in (select a from t2); -- 这个是一条独立的子查询，时间复杂度 O(M+N)
  2. --
  3. -- 经过优化器重写后
  4. --
  5. select a from t1 where exists (select 1 from t2 where t1.a = t2.a); -- 这是相关子查询，复杂度O(M*N + M)

所以在 MySQL 5.6 之前，部分的子查询需要重写成 join 的形式 （注意表的大小）。

  1. mysql> select t1.a from t1 join t2 on t1.a = t2.a;
  2. +------+
  3. | a    |
  4. +------+
  5. |    4 |
  6. +------+
  7. 1 row in set (0.00 sec)

MySQL 5.6 之后

在 MySQL 5.6 之后，优化器不会将子查询重写成 exists 的形式，而是自动优化，性能有了大幅提升，可通过 explain extended 来查看子查询优化的结果。

explain extended 扩展能够在原本 explain 的基础上额外提供一些查询优化的信息，这些信息可以通过 MySQL 的show warnings 命令得到。

  1. mysql> EXPLAIN EXTENDED SELECT * FROM employees t WHERE t.`first_name` IN ('Georgi','Bezalel','Parto','Chirstian');
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  5. |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299246 |    40.00 | Using where |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  7. 1 row in set, 2 warnings (0.00 sec)
  9. mysql> SHOW WARNINGS;
  10. +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. | Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                                                           |
  12. +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                                                                                                                                                                                                                                 |
  14. | Note    | 1003 | /* select#1 */ select `employees`.`t`.`emp_no` AS `emp_no`,`employees`.`t`.`birth_date` AS `birth_date`,`employees`.`t`.`first_name` AS `first_name`,`employees`.`t`.`last_name` AS `last_name`,`employees`.`t`.`gender` AS `gender`,`employees`.`t`.`hire_date` AS `hire_date` from `employees`.`employees` `t` where (`employees`.`t`.`first_name` in ('Georgi','Bezalel','Parto','Chirstian')) |
  15. +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. 2 rows in set (0.00 sec)

包含 NULL 值的 NOT IN

  1. mysql> select null in ('a', 'b', null);
  2. +--------------------------+
  3. | null in ('a', 'b', null) |
  4. +--------------------------+
  5. |                     NULL |
  6. +--------------------------+
  7. 1 row in set (0.00 sec)

MySQL 数据库的比较操作，除了返回 1（True）, 0（False） 之外，还会返回 NULL，NULL 和 NULL 的比较，返回的还是 NULL。

  1. mysql> select null not in ('a', 'b', null);  
  2. +------------------------------+
  3. | null not in ('a', 'b', null) |
  4. +------------------------------+
  5. |                         NULL |  -- null不在('a', 'b', null)中，返回的还是null，因为有null和null的比较
  6. +------------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select 'a' not in ('a', 'b', null);  
  10. +-----------------------------+
  11. | 'a' not in ('a', 'b', null) |
  12. +-----------------------------+
  13. |                           0 |  -- a不在('a', 'b', null)中，返回0，即False
  14. +-----------------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> select 'c' not in ('a', 'b');
  18. +-----------------------+
  19. | 'c' not in ('a', 'b') |
  20. +-----------------------+
  21. |                     1 |  -- 这个返回值可以理解'c'不在('a', 'b')中，返回1，即为True
  22. +-----------------------+
  23. 1 row in set (0.00 sec)
  25. mysql> select 'c' not in ('a', 'b', null); 
  26. +-----------------------------+
  27. | 'c' not in ('a', 'b', null) |
  28. +-----------------------------+
  29. |                        NULL | -- 理论上应该是返回1，即True的。但是包含了null值，则返回null
  30. +-----------------------------+
  31. 1 row in set (0.00 sec)

对于包含了 NULL 值的 IN 操作，总是返回 True 或者 NULL，NOT IN 返回 NOT True（False）或者 NOT NULL（NULL）。

  1. --
  2. -- SQL语句一 使用 EXISTS
  3. --
  4. select customerid, companyname 
  5.     from customers as A
  6.     where country = 'Spain' 
  7.         and not exists
  8.             ( select * from orders as B
  9.               where A.customerid = B.customerid );
  11. --
  12. -- SQL语句二 使用 IN
  13. --
  14. select customerid, companyname 
  15.     from customers as A
  16.     where country = 'Spain' 
  17.         and customerid not in (select customerid from orders);
  19. -----
  20. -- 当结果集合中没有NULL值时，上述两条SQL语句查询的结果是一致的 
  21. -----
  23. --
  24. -- 插入一个NULL值
  25. --
  26. insert into orders(orderid) values (null);
  28. -----
  29. -- SQL语句1 : 返回和之前一致
  30. -- SQL语句2 : 返回为空表，因为子查询返回的结果集中存在NULL值。not in null 永远返回False或者NULL
  31. --            此时 where (country = 'Spain' and (False or NULL)) 为 False OR NULL，条件永远不匹配
  32. -----
  34. --
  35. -- SQL语句2 改写后
  36. --
  37. select customerid, companyname 
  38.     from customers as A
  39.     where country = 'Spain' 
  40.         and customerid not in (select customerid from orders 
  41.                                 where customerid is not null);  -- 增加这个过滤条件，使用is not，而不是<>
  44. --
  45. -- 和 null比较，使用is和is not， 而不是 = 和 <>
  46. --
  47. mysql> select null = null; 
  48. +-------------+
  49. | null = null |
  50. +-------------+
  51. |        NULL |
  52. +-------------+
  53. 1 row in set (0.00 sec)
  55. mysql> select null <> null;
  56. +--------------+
  57. | null <> null |
  58. +--------------+
  59. |         NULL |
  60. +--------------+
  61. 1 row in set (0.00 sec)
  63. mysql> select null is null; 
  64. +--------------+
  65. | null is null |
  66. +--------------+
  67. |            1 |  -- 返回 True
  68. +--------------+
  69. 1 row in set (0.00 sec)
  71. mysql> select null is not null;
  72. +-------------------+
  73. | null is not  null |
  74. +-------------------+
  75. |                 0 |  -- 返回 False
  76. +-------------------+
  77. 1 row in set (0.00 sec)

EXISTS 不管返回值是什么，而是看是否有行返回，所以 EXISTS 中子查询都是 select *、select 1 等，因为只关心返回是否有行（结果集）。

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/bpx24o 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。