MySQL关键字EXISTS和IN

在学习 Laravel 的 has 和 whereHas 的时候打印了下执行的 sql 语句。发现生成的语句使用的是 exists 从句的子查询。对于在 mysql 中还尚未使用过 exists 这个关键字，所以就仔细的研究了下。

最后发现说的最多的还是拿来和 IN 来做比较，所以也就有了下文。

Laravel 的 has 和 whereHas

• has

  $post = Post::has('comments', '>=', 1)->get(['title']);
  

  SELECT `title`
  FROM `posts`
  WHERE EXISTS (
      SELECT *
      FROM `comments`
      WHERE `posts`.`id` = `comments`.`post_id`
  )
  

• whereHas

  whereHas 其实在底层也是调用了 has 方法，只是将回调函数的位置向前提了。

  $post = Post::whereHas('comments', function($query) {
      $query->where('content', 'like', '%1%');
  }, '>=', 1)->get();
  

  SELECT *
  FROM `posts`
  WHERE EXISTS (
      SELECT *
      FROM `comments`
      WHERE `posts`.`id` = `comments`.`post_id`
          AND `content` LIKE '%1%'
  )
  

EXISTS

概述

当 exists 里的条件语句能够返回记录时 (无论记录行多少，只要能返回)，条件就为真，返回当前 loop 到的这条记录。反之如果 exists 里的条件语句不能返回记录行，则条件为假，那么当前 loop 到的这条记录被丢弃。

exists 的条件就像是一个 boolean 条件，当有结果集则为 true，不能返回结果集则为 false.

语法格式

SELECT * FROM TABLE_NAME WHERE [NOT] EXISTS (SELECT...)

示例

SELECT *
FROM `posts`
WHERE EXISTS (
    SELECT 1
    FROM `comments`
    WHERE comments.post_id = posts.id
)

从这里也可以看出 exists 里的条件语句 SELECT 1 即可以是 1 也可以是 * . 因为只要有结果集返回就可以。

上面这条语句表达的是，如果哪篇文章有评论，那么就显示出来。

总的来说，如果 posts 表中有 n 条记录，那么 exists 查询的就是将这 n 条记录逐条取出，然后判断 n 遍 exists 条件。如果 exists 为 true 就放入结果集，反之丢弃。

IN

语法格式

SELECT * FROM TABLE_NAME WHERE `column` [NOT] IN (SELECT `column` FROM TABLE_NAME2)

需要注意的是，IN 所对应的 select 语句返回的结果一定是一列，但可以为多行。

示例

SELECT ＊ FROM `users` WHERE `id` IN (SELECT `id` FROM `user_score` WHERE `score` > 60)

查询得分超过 60 分的用户信息。

EXISTS 与 IN 的关系和区别

select * from p_user_2 
where id [not] in (select id from p_user );

select * from p_user_2 
where [not] EXISTS (select id from p_user where id = p_user_2.id )

这 2 条语句都可以达到同样的效果。那么一般什么时候用 exists，什么时候使用 in 呢？

• 如果查询的 2 个表大小相当，那么使用 exists 或者 in，差别不大；
• 如果 2 个表中，一个是小表，一个是大表，则子查询表大的用 exists，子查询表小的用 in；

例如：表 A（小表），表 B（大表）

子查询表为表 B

select * from A 
where cc in (select cc from B) 
//效率低，用到了A表上cc列的索引；
 
select * from A 
where exists(select cc from B where cc=A.cc) 
//效率高，用到了B表上cc列的索引。 

子查询表为表 A

select * from B 
where cc in (select cc from A) 
//效率高，用到了B表上cc列的索引；
 
select * from B 
where exists(select cc from A where cc=B.cc) 
//效率低，用到了A表上cc列的索引。

总结

NOT IN 和 NOT EXISTS，如果查询语句使用了 NOT IN，那么内外表都会进行全表扫描，没有用到索引；而 NOT EXISTS 的子查询依然能用到表上的索引。所以无能哪个表大，用 NOT EXISTS 都比使用 NOT IN 要快。