MYSQL - IN和EXIST之间的区别 [英] MYSQL - Difference between IN and EXIST
问题描述
MySql问题：
在MySql中执行子查询时，[NOT] IN和[NOT] EXIST之间的区别是什么。
<
EXISTS字面上是用于检查是否存在指定的条件。在当前的标准SQL中，它将允许您指定多个条件进行比较 - 如果您想知道col_a和col_b两者是否匹配，这使得它比IN子句强一点。 MySQL IN支持元组，但语法不可移植，所以EXISTS是一个更好的选择，以便于可读性和可移植性。
EXISTS要注意的另一件事是如何它操作 - EXISTS返回一个布尔值，并将在第一个匹配返回布尔值。所以如果你处理重复/多次，EXISTS将比IN或JOINs根据数据和需要更快的执行。
IN
IN是OR子句的语法糖。虽然它非常宽松，但是处理大量的值比较（在1,000以北）有问题。
NOT
NOT运算符只是反转逻辑。
子查询vs JOINs
连接是有缺陷的，因为如果父对象有多个子记录，JOIN会冒险增加结果集。是的，你可以使用DISTINCT或GROUP BY来处理这个问题，但这很可能会使使用JOIN的性能受益。知道你的数据，以及你想要的结果集 - 这些都是编写性能良好的SQL的关键。
重申知道什么时候和为什么要使用 - LEFT JOIN IS NULL是MySQL上最快的排除列表 如果所比较的列是不可空的 ，否则NOT IN / NOT EXISTS是更好的选择。
参考：
- MySQL：LEFT JOIN / IS NULL，NOT IN，NOT EXISTS on nullable columns < a>
- MySQL：LEFT JOIN / IS NULL，NOT IN，NOT EXISTS on NOT nullable columns
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
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:
- MySQL: LEFT JOIN/IS NULL, NOT IN, NOT EXISTS on nullable columns
- MySQL: LEFT JOIN/IS NULL, NOT IN, NOT EXISTS on NOT nullable columns
这篇关于MYSQL - IN和EXIST之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
