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要快。