LEFT-JOIN而不是oracle (+) with where子句

Question

最后一篇帖子被删除，因为" duplicate ";文章(10年前)，但我不能适应我的问题的答案。

我想用LEFT JOIN代替oracle(+)，但是我想不出来。

FROM a,b,c
WHERE b.a  = '101'
AND a.a    = '202'
AND b.c    = a.c
AND a.d    = c.d(+)
AND ROWNUM = 1;

我试过的是

FROM a
LEFT JOIN c
ON a.d = c.d
WHERE b.a  = '101'
AND a.a    = '202'
AND b.c    = a.c
AND ROWNUM = 1;

，但由于我从未从表b中获取数据，它失败了。

Answer 1

您想要将INNER JOIN变为b，然后将LEFT OUTER JOIN变为c:

FROM  a
INNER JOIN b
ON (b.c = a.c)
LEFT OUTER JOIN c
ON (a.d = c.d)
WHERE b.a  = '101'
AND   a.a    = '202'
AND   ROWNUM = 1;

你查询:

---

FROM a,b,c
WHERE b.a  = '101'
AND a.a    = '202'
AND b.c    = a.c
AND a.d    = c.d(+)
AND ROWNUM = 1;

FROM子句中的逗号表示CROSS JOIN。

然而，在WHERE子句中，您有:

• b.c = a.c，将表a和表b的连接条件从CROSS JOIN修改为INNER JOIN;和
• a.d = c.d(+)将a和c表之间的连接条件从CROSS JOIN更改为LEFT OUTER JOIN。