MySQL关键字EXISTS和IN

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

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

Laravel的has和whereHas

  • has

    1
    $post = Post::has('comments', '>=', 1)->get(['title']);
    1
    2
    3
    4
    5
    6
    7
    SELECT `title`
    FROM `posts`
    WHERE EXISTS (
    SELECT *
    FROM `comments`
    WHERE `posts`.`id` = `comments`.`post_id`
    )
  • whereHas

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

    1
    2
    3
    $post = Post::whereHas('comments', function($query) {
    $query->where('content', 'like', '%1%');
    }, '>=', 1)->get();
    1
    2
    3
    4
    5
    6
    7
    8
    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.

语法格式

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

示例

1
2
3
4
5
6
7
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

语法格式

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

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

示例

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

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

EXISTS与IN的关系和区别

1
2
3
4
5
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

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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要快。