用于外连接 (+) 的旧 Oracle 语法是否始终等同于新语法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7907562/
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
Is an old Oracle syntax for outer joins (+) always equivalent to new syntax?
提问by a1ex07
I wonder if it's always possible to rewrite LEFT JOINusing old Oracle syntax(+). In particular, I tried to express the following query using (+)
我想知道是否总是可以LEFT JOIN使用旧的 Oracle 语法进行重写(+)。特别是，我尝试使用以下查询来表达(+)
SELECT *
FROM table_1 a
LEFT JOIN table_2 b ON (b.table1_id = a.id AND b.other_field = 'value1')
without success. Is it possible at all?
Thank you.
没有成功。有可能吗？
谢谢你。
回答by StevieG
I'm guessing you're not using the (+) operator in the test of b.other_field.. This should work:
我猜你没有在 b.other_field 的测试中使用 (+) 运算符..这应该有效：
SELECT *
FROM table_1 a, table_2 b
WHERE b.table1_id(+) = a.id
AND b.other_field(+) = 'value1'
回答by Mike Sherrill 'Cat Recall'
If I recall correctly, it's not alwayspossible to rewrite an ANSI join in Oracle's old outer join syntax, because the order of execution can change the rows returned.
如果我没记错的话，并不总是可以用 Oracle 的旧外连接语法重写 ANSI 连接，因为执行顺序可以更改返回的行。
What does "without success" mean? Did you get an error? Did you get the wrong rows? Did you get the wrong columns?
“没有成功”是什么意思？你有没有遇到错误？你得到错误的行了吗？你弄错了列吗？
A left join will preserve all the rows in table_1. The basic form of old-style Oracle syntax is a Cartesian product with a WHERE clause, and a "+" token on the other table. (This doesn't include your entire WHERE clause. That's deliberate.)
左连接将保留 table_1 中的所有行。旧式 Oracle 语法的基本形式是带有 WHERE 子句和另一个表上的“+”标记的笛卡尔积。（这不包括您的整个 WHERE 子句。这是故意的。）
SELECT *
FROM table_1 a, table_2 b
WHERE a.id = b.table1_id(+)
See, for example, AskTom.
例如，参见AskTom。
For troubleshooting . . .
用于故障排除。. .
If you start with your query
如果您从查询开始
SELECT *
FROM table_1 a
LEFT JOIN table_2 b ON (b.table1_id = a.id AND b.other_field = 'value1')
and eliminated the aliases, you'd have
并消除了别名，你就会有
SELECT *
FROM table_1
LEFT JOIN table_2 ON (table_2.table1_id = table_1.id AND
table_2.other_field = 'value1')
Are there actually columns named table_2.table1_idand table_1.id? Does that work?
实际上是否有名为table_2.table1_idand 的列table_1.id？那样有用吗？
If that's not the problem start simpler. Try this.
如果这不是问题，那就更简单了。尝试这个。
SELECT table_1.id, table_2.table1_id
FROM table_1
INNER JOIN table_2 ON (table_2.table1_id = table_1.id);
Does that work? Next try this.
那样有用吗？接下来试试这个。
SELECT table_1.id, table_2.table1_id
FROM table_1
LEFT JOIN table_2 ON (table_2.table1_id = table_1.id);
If that works, try adding the rest of your JOIN clause.
如果可行，请尝试添加 JOIN 子句的其余部分。
