laravel mysql exists_MySQL关键字EXISTS和IN

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

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

Laravel的has和whereHashas$post = Post::has('comments', '>=', 1)->get(['title']);SELECT `title`

FROM `posts`

WHERE EXISTS (

SELECT *

FROM `comments`

WHERE `posts`.`id` = `comments`.`post_id`

)whereHaswhereHas其实在底层也是调用了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(大表)

子查询表为表Bselect * 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列的索引。

子查询表为表Aselect * 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要快。