mysql in or exist_MYSQL - IN和EXIST之间的区别

最新推荐文章于 2021-01-27 15:18:28 发布

ForAui

最新推荐文章于 2021-01-27 15:18:28 发布

阅读量67

点赞数

文章标签： mysql in or exist

本文链接：https://blog.csdn.net/weixin_36212212/article/details/113131708

版权

MySql question:

What is the difference between [NOT] IN and [NOT] EXIST when doing subqueries in MySql.

解决方案

EXISTS

EXISTS literally is for checking for the existence of specified criteria. In current standard SQL, it will allow you to specify more than one criteria for comparison - IE if you want to know when col_a and col_b both match - which makes it a little stronger than the IN clause. MySQL IN supports tuples, but the syntax is not portable, so EXISTS is a better choice both for readability and portability.

The other thing to be aware of with EXISTS is how it operates - EXISTS returns a boolean, and will return a boolean on the first match. So if you're dealing with duplicates/multiples, EXISTS will be faster to execute than IN or JOINs depending on the data and the needs.

IN is syntactic sugar for OR clauses. While it's very accommodating, there are issues with dealing with lots of values for that comparison (north of 1,000).

NOT

The NOT operator just reverses the logic.

Subqueries vs JOINs

The mantra "always use joins" is flawed, because JOINs risks inflating the result set if there is more than one child record against a parent. Yes, you can use DISTINCT or GROUP BY to deal with this, but it's very likely this renders the performance benefit of using a JOIN moot. Know your data, and what you want for a result set - these are key to writing SQL that performs well.

To reiterate knowing when and why to know what to use - LEFT JOIN IS NULL is the fastest exclusion list on MySQL if the columns compared are NOT nullable, otherwise NOT IN/NOT EXISTS are better choices.

Reference: