SQL Oracle：多列的快速 NOT IN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4345565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Fast NOT IN for multiple columns
提问by levanovd
I need to synchronize two tables. Let's assume that the tables contain following columns:
我需要同步两个表。假设这些表包含以下列：
Table1: A, B, C, D
Table2: A, B, C, E
I need to find such rows in Table1 that there isn't entry with corresponding (A, B, C)values in Table2, then calculate E as F(D) and update Table2.
我需要在 Table1 中找到这样的行，在 Table2 中没有对应(A, B, C)值的条目，然后将 E 计算为 F(D) 并更新 Table2。
If I need to match e.g. only A, I would write the following query:
如果我只需要匹配例如 A，我将编写以下查询：
SELECT * FROM Table1 WHERE A NOT IN (SELECT A FROM Table2)
Multi-column analog seems to be too slow:
多列模拟似乎太慢了：
SELECT * FROM Table1 WHERE A NOT IN (SELECT A FROM Table2)
AND B NOT IN (SELECT B FROM Table2)
AND C NOT IN (SELECT C FROM Table2)
What is the best way to write such query?
编写此类查询的最佳方法是什么？
回答by Ronnis
If (a,b,c) are NOT NULL in both tables, both NOT IN and NOT EXISTS will most likely (on the verisons I have tried) generate the same execution plan.
如果 (a,b,c) 在两个表中都不是 NULL，则 NOT IN 和 NOT EXISTS 很可能（在我尝试过的版本上）生成相同的执行计划。
If (a,b,c) are declared as nullable, but you knowthat the columns are in fact not null, you can trick the optimizer into doing the hash anti join anyway by adding "AND a is not null AND b is not null AND c is not null" to your query. (You may also have to add a /*+ HASH_AJ */ hint in the sub query.)
如果 (a,b,c) 被声明为可空，但您知道列实际上不为空，您可以通过添加“AND a is not null AND b is not null AND c 不为空”到您的查询中。（您可能还需要在子查询中添加 /*+ HASH_AJ */ 提示。）
Also, the following queries are NOT identical:
此外，以下查询不相同：
from table1
where (a,b,c) not in (select a,b,c from table2)
from table1
where a not in(select a from table2)
and b not in(select b from table2)
and c not in(select c from table2)
回答by Michael Pakhantsov
SELECT * FROM Table1
WHERE (A, B, C) NOT IN
(SELECT A,B,C FROM Table2)
回答by guigui42
SELECT * FROM Table1
WHERE
not exist (
SELECT 1 FROM Table2
where Table2.a=Table1.a
and Table2.b=Table1.b
and Table2.c=Table1.c )
EDIT : Note that Not exist and NOT IN are not totally identical in some cases (NULL values) see : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:442029737684
编辑：请注意，在某些情况下（空值）不存在和不完全相同，请参阅：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::p11_question_id :442029737684
回答by erbsock
You can try
你可以试试
SELECT * FROM Table1
WHERE
not exists (
SELECT 1 FROM Table2
where Table2.a=Table1.a
and Table2.b=Table1.b
and Table2.c=Table1.c );
as posted by guigui42. It does a hash join anti and avoids the filter.
由 guigui42 发布。它执行散列连接反并避免过滤器。
OR try
或尝试
select t1.*
from table1 t1, table2 t2
where t1.a = t2.a(+)
and t1.b = t2.b(+)
and t1.c = t2.c(+)
and (t2.a is null or t2.b is null or t2.c is null);
That does an outer join + filter. Both othese should be much faster than doing a NOT IN.
这做了一个外连接+过滤器。其他两者都应该比执行 NOT IN 快得多。
回答by LCC
A small addendum:I have found that Oracle (11gR1 in my case) refuses to hash anti join when the NOT IN clause contains more than one column, e.g.,
一个小附录：我发现当 NOT IN 子句包含多个列时，Oracle（在我的情况下为 11gR1）拒绝散列反连接，例如，
SELECT * FROM Table1 WHERE (A,B,C) NOT IN (
SELECT /*+ HASH_AJ */ A,B,C FROM Table2
WHERE A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL
)
and this even when adding one of the hints (same with UNNEST) and the non-NULL conditions. With one columns only it works.
即使在添加提示之一（与 相同UNNEST）和非 NULL 条件时也是如此。只有一列才有效。
回答by Tiago Medici
for performance reasons never do
出于性能原因从不做
NOT IN (SELECT
use
用
NOT EXISTS (SELECT 1 FROM
