oracle sql中的“哪里不存在”与“左外连接”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12526349/
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
"Where not exists" vs "left outer join" in oracle sql
提问by Ion C
I have a simple SQL query:
我有一个简单的 SQL 查询：
SELECT
columnA, columnB, columnC...
FROM
(SELECT
columnA, columnB, columnC...
FROM
SomeTable) Table1
WHERE NOT EXISTS
(SELECT
columnA
FROM
SomeOtherTable st
WHERE
st.columnB = Table1.columnB)
Can anyone give me a suggestion how to rewrite this query for better performance? I mean to include the WHERE NOT EXISTSclause in Table1.
谁能给我一个建议如何重写这个查询以获得更好的性能？我的意思是WHERE NOT EXISTS在表 1 中包含该子句。
回答by Daniel Hilgarth
You can use this:
你可以使用这个：
select Table1.*
from (select * from SomeTable) Table1
left outer join SomeOtherTable sot
on Table1.columnB = sot.columnB
where sot.columnB is null;
For the performance it is important to have indexes on columnBon both tables.
为了提高性能，columnB在两个表上都有索引很重要。
回答by TechDo
How about this:
这个怎么样：
SELECT columnA, columnB, columnC...
FROM SomeTable
WHERE (SELECT COUNT(*) FROM SomeOtherTable st WHERE st.columnB = SomeTable.columnB)=0;
