LEFT OUTER JOIN 与 NOT EXISTS 上的 SQL 性能 [英] SQL performance on LEFT OUTER JOIN vs NOT EXISTS

问题描述

如果我想在表 A 中查找一组条目但不在表 B 中，我可以使用 LEFT OUTER JOIN 或 NOT EXISTS.我听说 SQL Server 面向 ANSI，在某些情况下，LEFT OUTER JOIN 比 NOT EXISTS 高效得多.在这种情况下 ANSI JOIN 会表现得更好吗?并且通常在 SQL Server 上连接运算符比 NOT EXISTS 更有效?

If I want to find a set of entries in table A but not in table B, I can use either LEFT OUTER JOIN or NOT EXISTS. I've heard SQL Server is geared towards ANSI and in some case LEFT OUTER JOINs are far more efficient than NOT EXISTS. Will ANSI JOIN perform better in this case? and are join operators more efficient than NOT EXISTS in general on SQL Server?

推荐答案

Joe 的链接是一个很好的起点.Quassnoi 也涵盖了这一点.

Joe's link is a good starting point. Quassnoi covers this too.

一般来说，如果您的字段已正确编入索引，或者您希望过滤掉更多记录(即有很多行 EXIST> 在子查询中)NOT EXISTS 会表现得更好.

In general, if your fields are properly indexed, OR if you expect to filter out more records (i.e. have a lots of rows EXIST in the subquery) NOT EXISTS will perform better.

EXISTS 和 NOT EXISTS 都短路 - 一旦记录与条件匹配，它就会被包含或过滤掉，并且优化器会移动到下一条记录.

EXISTS and NOT EXISTS both short circuit - as soon as a record matches the criteria it's either included or filtered out and the optimizer moves on to the next record.

LEFT JOIN 将加入所有记录，不管它们是否匹配，然后过滤掉所有不匹配的记录.如果您的表很大和/或您有多个 JOIN 条件，这可能会非常占用资源.

LEFT JOIN will join ALL RECORDS regardless of whether they match or not, then filter out all non-matching records. If your tables are large and/or you have multiple JOIN criteria, this can be very very resource intensive.

我通常会尽量使用 NOT EXISTS 和 EXISTS.对于 SQL Server，IN 和 NOT IN 在语义上是等效的，并且可能更容易编写.这些是您可以在 SQL Server 中找到的唯一可以保证短路的运算符.

I normally try to use NOT EXISTS and EXISTS where possible. For SQL Server, IN and NOT IN are semantically equivalent and may be easier to write. These are among the only operators you will find in SQL Server that are guaranteed to short circuit.

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