SQL:排除性能比较(加入与未加入) [英] SQL: Performance comparison for exclusion (Join vs Not in)

问题描述

我对查询sql的最有效方法感到好奇.例如.有2个表(tableA和tableB)可以在1列(col1)上连接.我想为colB在tableB中不存在的所有行显示tableA的数据.

I am curious on the most efficient way to query exclusion on sql. E.g. There are 2 tables (tableA and tableB) which can be joined on 1 column (col1). I want to display the data of tableA for all the rows which col1 does not exist in tableB.

(因此，换句话说，tableB包含tableA的col1的子集.我想显示tableA而不显示tableB中存在的数据)

(So, in other words, tableB contains a subset of col1 of tableA. And I want to display tableA without the data that exists in tableB)

让我们说tableB有100行，而tableA是巨大的(超过1M行).我知道可以使用不存在(不存在)"，但是也许有更有效的方法(较少的计算时间)来做到这一点.我可能没有外部联接吗?

Let's say tableB has 100 rows while tableA is gigantic (more than 1M rows). I know 'Not in (not exists)' can be used but perhaps there are more efficient ways (less comp. time) to do it.? I don't maybe with outer joins?

代码片段和注释非常感谢.

Code snippets and comments are much appreciated.

推荐答案

取决于RDBMS.对于Microsoft SQL Server 不存在是首选，因为它可以使用更有效的反半连接.

Depends on the RDBMS. For Microsoft SQL Server NOT EXISTS is preferred to the OUTER JOIN as it can use the more efficient Anti-Semi join.

对于Oracle 负号"显然比不存在"更合适

您需要查看执行计划并做出决定.

You would need to look at the execution plans and decide.

这篇关于SQL:排除性能比较(加入与未加入)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！