Oracle 专有连接 - 在多个条件下加入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5833699/
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 proprietary joins- joining on multiple conditions
提问by komedit1
I've the follwing 2 versions of ANSI compliant SQL(column/table names changed to protect confidential data), of which one of them satisfies my requirement by following the right logic while the other doesn't.
我有以下 2 个版本的 ANSI 兼容 SQL（更改列/表名称以保护机密数据），其中一个通过遵循正确的逻辑满足我的要求，而另一个不满足。
1)ANSI Join 1-Works
1)ANSI 加入 1-作品
SELECT b.COLUMN_A,
COUNT(a.COLUMN_A)
FROM TABLE1 a
RIGHT OUTER JOIN
(SELECT COLUMN_A FROM TABLE2 WHERE COLUMN_X='TEST') b
ON a.COLUMN_A = b.COLUMN_A
AND a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5) --WORKS
GROUP BY b.COLUMN_A
1) gives output like this:
1）给出这样的输出：
COLUMN_A COUNT(COLUMN_A)
--------------------------
A 0
B 0
C 1
D 1
E 0
2)ANSI Join 2-Doesn't work
2）ANSI Join 2-不起作用
SELECT b.COLUMN_A,
COUNT(a.COLUMN_A)
FROM TABLE1 a
RIGHT OUTER JOIN
(SELECT COLUMN_A FROM TABLE2 WHERE COLUMN_X='TEST') b
ON a.COLUMN_A = b.COLUMN_A
WHERE
a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5) --DOESN'T WORK
GROUP BY b.COLUMN_A
3)Oracle's proprietary join-Doesn't work
3）Oracle的专有join-不起作用
SELECT b.COLUMN_A,
COUNT(a.COLUMN_A)
FROM TABLE1 a,(SELECT COLUMN_A FROM TABLE2 WHERE COLUMN_X='TEST') b
WHERE
a.COLUMN_A(+) = b.COLUMN_A
AND a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5) --DOESN'T WORK
GROUP BY b.COLUMN_A
2) & 3) gives output like this:
2) & 3) 给出这样的输出：
COLUMN_A COUNT(COLUMN_A)
--------------------------
C 1
D 1
I understand (2,ANSI) & (3,PROPRIETARY) are equivalent. But Is there any equivalent proprietary SQL for (1,ANSI)?. Any help would be most welcome. Thanks. Edit: I've updated the question with the sample output.
我明白 (2,ANSI) & (3,PROPRIETARY) 是等价的。但是对于 (1,ANSI) 是否有任何等效的专有 SQL？。任何帮助将是最受欢迎的。谢谢。编辑：我已经用示例输出更新了问题。
回答by Dave Costa
You probably want to change this condition:
您可能想要更改此条件：
AND a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5)
to either this:
要么：
AND a.COLUMN_B (+) in (SELECT FROM TABLE3 WHERE COLUMN_Y=5)
or this:
或这个：
AND (a.COLUMN_B IS NULL OR a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5))
But in general, I would say do outer joins with the ANSI syntax. Even as someone who learned Oracle syntax first and is very comfortable with it, ANSI is much clearer for outer joins.
但总的来说，我会说使用 ANSI 语法进行外连接。即使对于首先学习 Oracle 语法并且对它非常熟悉的人，ANSI 对于外连接也更加清晰。
