oracle 具有空值的左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31422466/
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
left outer join with null values
提问by Nancy Guruswamy
**Table1** **Table2**
ID Values ID Values
1 100 1 10
2 200 2 20
3 300 3 30
4 400 4 40
null 2000 null 3000
5 500
o/p:-
o/p:-
ID Table1_Values Table2_Values
1 100 10
2 200 20
3 300 30
4 400 40
5 500 null
null 2000 3000
回答by Ravi
Try this ..
尝试这个 ..
select t1.id,t1.values,t2.values from
table1 t1
left outer join
table t2 on nvl(t1.id,0)=nvl(t2.id,0)
回答by MT0
You can add a check to see if both values are NULLto the join condition:
您可以添加检查以查看两个值是否都NULL符合连接条件：
SELECT t1.ID,
t1.VALUES AS Table1Values,
t2.VALUES AS Table2Values
FROM TABLE2 t1
LEFT OUTER JOIN
TABLE2 t2
ON ( t1.ID = t2.ID OR ( t1.ID IS NULL AND t2.ID IS NULL ) )
回答by David Aldridge
There is an Oracle function Sys_Op_Map_NonNull that has been used for many versions as part of a materialised view refresh query for just this purpose.
有一个 Oracle 函数 Sys_Op_Map_NonNull 已用于许多版本，作为物化视图刷新查询的一部分，仅用于此目的。
https://oraclesponge.wordpress.com/2006/04/12/a-quick-materialized-view-performance-note/
https://oraclesponge.wordpress.com/2006/04/12/a-quick-materialized-view-performance-note/
It used to be entirely undocumented, but is now mentioned as a means of optimising fast refresh: http://docs.oracle.com/database/121/DWHSG/basicmv.htm
它曾经完全没有记录，但现在被提及作为优化快速刷新的一种方式：http: //docs.oracle.com/database/121/DWHSG/basicmv.htm
So you could:
所以你可以：
select ...
from t1 left outer join t2 on (sys_op_map_nonnull(t1.id) = sys_op_map_nonnull(t2.id))
If you were joining a small set of the table then function-based indexes would help, but I wouldn't bother if you're joining all rows.
如果您加入一小组表，那么基于函数的索引会有所帮助，但如果您加入所有行，我不会打扰。
