带有等于和 in 的 sql 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9476332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql statements with equals vs in
提问by sgtz
Say that someone came up to you and said we're going to cut down the amount of SQL that we write by replacing equals with IN. The use would be both for single scalar values and lists of numbers.
假设有人走过来告诉您，我们将通过将 equals 替换为 来减少我们编写的 SQL 数量IN。可用于单个标量值和数字列表。
SELECT *
FROM table
WHERE id = 1
OR
或者
SELECT *
FROM table
WHERE id IN (1)
Are these statement equivalent to what the optimizer produces?
这些语句是否等同于优化器生成的语句？
This looks really simple on the surface, but it leads to simplification for two reasons: 1. large blocks of SQL don't need to be duplicated, and 2. we don't overuse dynamic SQL.
这从表面上看确实很简单，但由于两个原因导致简化：1. 不需要复制大块的 SQL，以及 2. 我们不会过度使用动态 SQL。
This is a contrived example, but consider the following.
这是一个人为的例子，但请考虑以下内容。
select a.* from tablea a
join tableb b on a.id = b.id
join tablec c on b.id2 = c.id2
left join tabled d on c.id3 = c.id3
where d.type = 1
... and the same again for the more than one case
...对于不止一种情况，同样如此
select a.* from tablea a
join tableb b on a.id = b.id
join tablec c on b.id2 = c.id2
left join tabled d on c.id3 = c.id3
where d.type in (1,2,3,4)
(this isn't even a large statement)
（这甚至不是一个大声明）
conceivably you could do string concatenation, but this isn't desirable in light of ORM usage, and dynamic SQL string concatenation always starts off with good intentions (at least in these parts).
可以想象，您可以进行字符串连接，但考虑到 ORM 的使用情况，这是不可取的，并且动态 SQL 字符串连接总是以良好的意图开始（至少在这些部分）。
采纳答案by Jeremy Wiggins
The two will produce the same execution plan - either a table scan, index scan, or index seek, depending on if/how you have your table indexed.
这两个会产生相同的执行计划-无论是table scan，index scan或index seek取决于是否/如何你有你的表建立索引。
You can see for yourself - Displaying Graphical Execution Plans (SQL Server Management Studio)- See the section called "Using the Execution Plan Options".
您可以亲自查看 -显示图形执行计划 (SQL Server Management Studio)- 请参阅名为“使用执行计划选项”的部分。
回答by Aaron Bertrand
Those two specific statements are equivalent to the optimizer (did you compare the execution plans?), but I think the more important benefit you get out of the latter is that
这两个特定语句等效于优化器（您是否比较了执行计划？），但我认为您从后者中获得的更重要的好处是
WHERE id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5
Can be expressed as the following, much more concise and readable (but semantically equivalent to the optimizer) version:
可以表示为以下更简洁易读（但语义上等同于优化器）的版本：
WHERE id IN (1,2,3,4,5)
The problem is that when expressed as the latter most people think they can pass a string, like @list = '1,2,3,4,5'and then say:
问题是当表示为后者时，大多数人认为他们可以传递一个字符串，@list = '1,2,3,4,5'然后说：
WHERE id IN (@list)
This does not work because @listis a single scalar string, and not an array of integers.
这不起作用，因为@list它是单个标量字符串，而不是整数数组。
For cases where you have a single value, I don't see how that "optimization" helps anything. You haven't written less SQL, you've actually written more. Can you outline in more detail how this is going to lead to less SQL?
对于只有一个值的情况，我看不出这种“优化”有什么帮助。你没有写更少的 SQL，你实际上写了更多。您能否更详细地概述这将如何导致更少的 SQL？
