子查询相关的in和exists区别

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

in 和 exists

在涉及子查询的过程中，通常使用in或者exists来进行连接，但是在一些情况下二者的性能和查询结果又显著不同，在这里加以记录。

---

一、场景一： 大批量数据的关联子查询

数据库简单介绍：一个简单的订单表，数据有1500000条。

(root@localhost) [dbt3]>desc orders;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| o_orderkey      | int(11)     | NO   | PRI | NULL    |       |
| o_custkey       | int(11)     | YES  | MUL | NULL    |       |
| o_orderstatus   | char(1)     | YES  |     | NULL    |       |
| o_totalprice    | double      | YES  |     | NULL    |       |
| o_orderDATE     | date        | YES  | MUL | NULL    |       |
| o_orderpriority | char(15)    | YES  |     | NULL    |       |
| o_clerk         | char(15)    | YES  |     | NULL    |       |
| o_shippriority  | int(11)     | YES  |     | NULL    |       |
| o_comment       | varchar(79) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

需求说明：查询每月最后一天的详细销售情况。

in的实现方法

# 下述例子为不相关子查询，用时短，效率高，推荐使用
SELECT 
  *
FROM
  orders o1 
WHERE o_orderDATE IN 
  (SELECT 
    MAX(o_orderDATE) AS maxdate 
  FROM
    orders 
  GROUP BY (DATE_FORMAT(o_orderDATE, '%Y%M'))) 
ORDER BY o_orderDATE ;

查询性能为：
执行耗时 : 1.719 sec
传送时间 : 0.296 sec
总耗时 : 2.016 sec

exists方法实现

SELECT 
  o_clerk,
  o_orderdate 
FROM
  orders o1 
WHERE EXISTS 
  (SELECT 
    MAX(o_orderDATE) AS maxdate 
  FROM
    orders 
  GROUP BY (DATE_FORMAT(o_orderDATE, '%Y%M')) 
  HAVING o1.`o_orderDATE` = maxdate) 
ORDER BY o_orderDATE ;

查询不到具体内容，耗时及其严重。

结果比较

在这种大规模的数据上，若利用exists进行相关子查询，每一条外部的查询都要和所有的1500000进行配对比较是非常不现实的，当遇到这种情况时，应当使用in进行非相关子查询。

---

二、场景二： not in 和 not exists的比较

用例数据库简介

代码如下（示例）：

(root@localhost) [dbt3]>select * from a;
+--------+-------+---------------------+
| userid | price | date                |
+--------+-------+---------------------+
|      1 |   100 | 2016-02-01 00:00:00 |
|      1 |    50 | 2016-02-01 00:00:00 |
|      2 |    50 | 2016-02-01 00:00:00 |
|      1 |   200 | 2016-03-01 00:00:00 |
|      2 |   200 | 2016-07-01 00:00:00 |
|      3 |   500 | 2016-09-01 00:00:00 |
+--------+-------+---------------------+

(root@localhost) [dbt3]>select * from b;
+------+
| y    |
+------+
|    1 |
|    2 |
| NULL |
+------+

需求说明：查询a表中userid不在b表y列中的数据详情。

not in实现

(root@localhost) [dbt3]>select * from a where userid not in (select y from b);
Empty set (0.00 sec)

可以看到，这样的查询方式无法返回数据为空。
问题出在表b中，因为b.y数据包含null，当使用 not in 进行查询时，若所查询目标不在被查询表中时，不会返回1而是返回null。可以通过以下sql语句进行验证：

(root@localhost) [dbt3]>select 'd' not in ('a','b','c');
+--------------------------+
| 'd' not in ('a','b','c') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

(root@localhost) [dbt3]>select 'd' not in ('a','b','c',NULL);
+-------------------------------+
| 'd' not in ('a','b','c',NULL) |
+-------------------------------+
|                          NULL |
+-------------------------------+
1 row in set (0.00 sec)

很明显，在上述被查询表中，有null值和没有null值返回的结果完全相反，所以在进行not in查询时，为了防止null值得干扰，应当在子查询中进行提前处理，去掉所有null值数据，如下：

(root@localhost) [dbt3]>select * from a where userid not in (select y from b where y is not null);
+--------+-------+---------------------+
| userid | price | date                |
+--------+-------+---------------------+
|      3 |   500 | 2016-09-01 00:00:00 |
+--------+-------+---------------------+
1 row in set (0.00 sec)

通过在子查询中引入限制条件，从而解决了上述问题。

not exists实现

not exists实现的方法并不会出现上述问题，按照正常步骤进行查询即可，不需要进行额外的判null处理。

(root@localhost) [dbt3]>select * from a where not exists (select y from b where a.userid = b.y);
+--------+-------+---------------------+
| userid | price | date                |
+--------+-------+---------------------+
|      3 |   500 | 2016-09-01 00:00:00 |
+--------+-------+---------------------+
1 row in set (0.00 sec)

---

总结

通常情况下使用 in 能解决大部分问题且效率较高，但是在使用过程中要注意内查询中的null值问题。