oracle 帮助调整不在和内部连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4814075/
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
Help regading not in and inner join
提问by Navin
I have two tables. Table1 and Table2
我有两张桌子。表 1 和表 2
Table1
表格1
id tid
1 100
2 200
3 300
Table2
表2
tid name
100 A
200 B
I want to take out id of records from Table1 whichever's tid is not present in Table2.
我想从表 1 中取出记录的 id，以表 2 中不存在的 tid 为准。
My output should be like this.
我的输出应该是这样的。
Table1.id
表1.id
3
For this i have written following queries but it is taking too much of time. Since both tables have more amount of records.
为此，我编写了以下查询，但花费了太多时间。由于两个表都有更多的记录。
please help me how to write a query for this such a way that it will take less amount of time.
请帮助我如何为此编写查询，以减少花费的时间。
select id from Table1 where tid not in (select tid from Table2)
select a.id from Table1 a inner join Table2 b on a.tid<>b.tid
TIA.
TIA。
回答by Damien_The_Unbeliever
Use a left join, and then use the WHERE clause to filter only to rows where the join didn't work:
使用左连接，然后使用 WHERE 子句仅过滤到连接不起作用的行：
SELECT
a.ID
from
Table1 a
left join
Table2 b
on
a.tid = b.tid
where
b.tid is null
Of course, this still might not work fast enough, in which case you need to check whether you have indexes on the tid columns in these two tables.
当然，这可能仍然不够快，在这种情况下，您需要检查这两个表中的 tid 列是否有索引。
回答by tbone
How about
怎么样
select id
from table1
where tid IN (
select tid from table1
minus
select tid from table2
)
set operations (minus part above) are pretty fast in Oracle
在 Oracle 中设置操作（减去上面的部分）非常快
回答by Benoit
First create indexes:
首先创建索引：
CREATE INDEX t1_tid ON table1 (tid ASC);
CREATE INDEX t2_tid ON table2 (tid ASC);
This way it could be faster.
这样它可以更快。
Regarding the query, what about:
关于查询，关于：
SELECT tid FROM table1
MINUS
SELECT tid FROM table2
回答by San
You can try "not exists" too:
您也可以尝试“不存在”：
select * from Table1 T1 where not exists (select 1 from Table2 T2 where T1.tid=t2.tid);
select * from Table1 T1 where not exist (select 1 from Table2 T2 where T1.tid=t2.tid);
