SQL JOIN 与 IN 性能? [英] SQL JOIN vs IN performance?

问题描述

我有一个案例，使用 JOIN 或 IN 会给我正确的结果......通常哪个性能更好，为什么?它在多大程度上取决于您运行的数据库服务器?(仅供参考，我使用的是 MSSQL)

I have a case where using a JOIN or an IN will give me the correct results... Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL)

推荐答案

一般来说，IN 和 JOIN 是不同的查询，可以产生不同的结果.

Generally speaking, IN and JOIN are different queries that can yield different results.

SELECT  a.*
FROM    a
JOIN    b
ON      a.col = b.col

与

SELECT  a.*
FROM    a
WHERE   col IN
        (
        SELECT  col
        FROM    b
        )

，除非 b.col 是唯一的.

然而，这是第一个查询的同义词:

However, this is the synonym for the first query:

SELECT  a.*
FROM    a
JOIN    (
        SELECT  DISTINCT col
        FROM    b
        )
ON      b.col = a.col

如果连接列是 UNIQUE 并标记为这样，则这两个查询在 SQL Server 中产生相同的计划.

If the joining column is UNIQUE and marked as such, both these queries yield the same plan in SQL Server.

如果不是，则 IN 在 DISTINCT 上比 JOIN 快.

If it's not, then IN is faster than JOIN on DISTINCT.

有关性能详细信息，请参阅我博客中的这篇文章:

See this article in my blog for performance details:

• IN对比 JOIN 对比 EXISTS

• IN vs. JOIN vs. EXISTS

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