SQL 中 EXISTS 和 IN 的区别? [英] Difference between EXISTS and IN in SQL?

问题描述

SQL 中的EXISTS 和IN 子句有什么区别?

What is the difference between the EXISTS and IN clause in SQL?

什么时候应该使用EXISTS，什么时候应该使用IN?

When should we use EXISTS, and when should we use IN?

推荐答案

exists 关键字可以这样使用，但实际上它是为了避免计数:

The exists keyword can be used in that way, but really it's intended as a way to avoid counting:

--this statement needs to check the entire table
select count(*) from [table] where ...

--this statement is true as soon as one match is found
exists ( select * from [table] where ... )

这在您有 if 条件语句时最有用，因为 exists 可以比 count 快得多.

This is most useful where you have if conditional statements, as exists can be a lot quicker than count.

in 最适合用于需要传递静态列表的地方:

The in is best used where you have a static list to pass:

 select * from [table]
 where [field] in (1, 2, 3)

当您在 in 语句中有一个表时，使用 join 更有意义，但大多数情况下这无关紧要.无论哪种方式，查询优化器都应该返回相同的计划.在某些实现中(大多是较旧的，例如 Microsoft SQL Server 2000)in 查询将始终获得 嵌套联接 计划，而join 查询将使用嵌套的合并 或 hash 视情况而定.更现代的实现更智能，即使使用 in 也可以调整计划.

When you have a table in an in statement it makes more sense to use a join, but mostly it shouldn't matter. The query optimiser should return the same plan either way. In some implementations (mostly older, such as Microsoft SQL Server 2000) in queries will always get a nested join plan, while join queries will use nested, merge or hash as appropriate. More modern implementations are smarter and can adjust the plan even when in is used.

这篇关于SQL 中 EXISTS 和 IN 的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！