oracle oracle左外连接不显示右空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/362556/
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 left outer joins not showing right null values
提问by shaunf
i'm having an issue with creating a query in oracle which doesnt seem to want to join on missing values
我在 oracle 中创建查询时遇到问题，该查询似乎不想加入缺失值
the table i have is this:
我的桌子是这样的：
table myTable(refnum, contid, type)
values are:
1, 10, 90000
2, 20, 90000
3, 30, 90000
4, 20, 10000
5, 30, 10000
6, 10, 20000
7, 20, 20000
8, 30, 20000
a break down of the fields i'm after is this:
我所追求的领域的细分是这样的：
select a.refnum from myTable a where type = 90000
select b.refnum from myTable b where type = 10000 and contid in (select contid from myTable where type = 90000)
select c.refnum from myTable c where type = 20000 and contid in (select contid from myTable where type = 90000)
the outcome of the query i'm after is this:
我所追求的查询结果是这样的：
a.refnum, b.refnum, c.refnum
i thought this would work:
我认为这会奏效：
select a.refnum, b.refnum, c.refnum
from myTable a
left outer join myTable b on (a.contid = b.contid)
left outer join myTable c on (a.contid = c.contid)
where a.id_tp_cd = 90000
and b.id_tp_cd = 10000
and c.id_tp_cd = 20000
so the values should be:
所以值应该是：
1, null, 6
2, 4, 7
3, 5, 8
but its only returning:
但它唯一的返回：
2, 4, 7
3, 5, 8
i thought left joins would show all values in the left and create a null for the right.
我认为左连接会显示左侧的所有值并为右侧创建一个空值。
help :(
帮助 ：（
回答by Tom Haigh
You are correct in saying that left joins will return nulls for the right where there is no match, but you are not allowing these nulls to be returned when you add this restriction to your where clause:
您说左连接将在没有匹配的情况下为右连接返回空值是正确的，但是当您将此限制添加到 where 子句时，您不允许返回这些空值：
and b.id_tp_cd = 10000
and c.id_tp_cd = 20000
You should be able to put these in the 'on' clause of the join instead, so only relevant rows on the right are returned.
您应该能够将它们放在连接的“on”子句中，因此只返回右侧的相关行。
select a.refnum, b.refnum, c.refnum
from myTable a
left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 10000)
left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000)
where a.id_tp_cd = 90000
回答by Trevor North
Or using the Oracle syntax instead of ansi
或者使用 Oracle 语法而不是 ansi
select a.refnum, b.refnum, c.refnum
from myTable a, mytable b, mytable c
where a.contid=b.contid(+)
and a.contid=c.contid(+)
and a.type = 90000
and b.type(+) = 10000
and c.type(+) = 20000;
REFNUM REFNUM REFNUM
---------- ---------- ----------
1 6
2 4 7
3 5 8
