带有等于和 in 的 sql 语句 [英] sql statements with equals vs in

问题描述

假设有人来找您说我们将通过用 IN 替换 equals 来减少我们编写的 SQL 数量.可用于单个标量值和数字列表.

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.

SELECT * 
  FROM table 
 WHERE id = 1

或

SELECT * 
  FROM table 
 WHERE id IN (1)

这些语句是否等同于优化器生成的语句?

Are these statement equivalent to what the optimizer produces?

这表面上看起来很简单，但由于两个原因导致简化:1. 不需要复制大块的 SQL，以及 2. 我们不会过度使用动态 SQL.

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.

这是一个人为的例子，但请考虑以下内容.

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)

可以想象你可以进行字符串连接，但鉴于 ORM 的使用，这不是可取的，并且动态 SQL 字符串连接总是以良好的意图开始(至少在这些部分).

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).

推荐答案

两者将产生相同的执行计划 - table scan、index scan 或 <代码>索引查找，取决于您是否/如何为您的表建立索引.

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.

您可以亲眼看看 - 显示图形执行计划 (SQL Server Management Studio) -请参阅使用执行计划选项"一节.

You can see for yourself - Displaying Graphical Execution Plans (SQL Server Management Studio) - See the section called "Using the Execution Plan Options".

这篇关于带有等于和 in 的 sql 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！