oracle 甲骨文外连接简写与上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3233220/
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 outer join short hand with upper
提问by learn_plsql
Works:
作品：
AND UPPER(a.name) = b.lname(+)
does not work
不起作用
AND UPPER(a.name) = UPPER(b.lname) (+)
Moving to ANSI joins is an option but a painstaking one. This code should be changed in lot of places and there are lot of joins. I would like to get this syntax correct and be on my way.
迁移到 ANSI 连接是一种选择，但却是一项艰苦的选择。这段代码应该在很多地方更改，并且有很多连接。我想让这个语法正确并在我的路上。
Is it possible?
是否可以？
回答by APC
Yuckiness aside, incorporating UPPER() with the old skool OUTER JOIN syntax is simplicity itself: we just need to get the brackets in the right order:
撇开讨厌不谈，将 UPPER() 与旧的 skool OUTER JOIN 语法结合起来本身就很简单：我们只需要按正确的顺序获得括号：
SQL> select t23.name
2 , d.university
3 from t23
4 , t_doctors d
5 where upper(t23.name) = upper(d.name(+))
6 order by t23.id
7 /
NAME UNIVERSITY
------------ --------------------
SAM-I-AM
MR KNOX
FOX IN SOCKS
CAT
LORAX
BILLY
MAISIE
DR SINATRA Whoville U
DR FONZ U of Grin-itch
PINNER BLINN
10 rows selected.
SQL>
Here is how to deploy the newer syntax with multiple tables:
以下是如何使用多个表部署较新的语法：
SQL> select t23.name
2 , d.university
3 , k.school
4 from t23
5 left outer join t_doctors d
6 on upper(t23.name) = upper(d.name)
7 left outer join t_kids k
8 on upper(t23.name) = upper(k.name)
9 order by t23.id
10 /
NAME UNIVERSITY SCHOOL
------------ -------------------- --------------------
SAM-I-AM Mulberry St Junior
MR KNOX
FOX IN SOCKS
CAT
LORAX
BILLY Roover River High
MAISIE Roover River High
DR SINATRA Whoville U
DR FONZ U of Grin-itch
PINNER BLINN
10 rows selected.
SQL>
回答by Jonathan Leffler
It is quite possible that the second version of the code will not work, ever. If it wasn't in use before, it is all the more plausible that it won't work.(See the accepted answer for how to use the obsolesent notation. I still think that the rest of the advice below stands - but note carefully the qualifier 'when you need to modify the SQL'; if you don't need to change the SQL for some other reason, there's no necessity to remove the old-style notation.)
很可能代码的第二个版本永远不会工作。如果它以前没有使用过，它就更可能不起作用。（有关如何使用过时的表示法，请参阅已接受的答案。我仍然认为下面的其余建议仍然有效 - 但请仔细注意限定符 ' when you need to modify the SQL'；如果您不需要更改 SQL由于某些其他原因，没有必要删除旧式符号。）
Bite the bullet and deal with the ANSI join when you need to do the case-insensitive comparison. Or investigate a locale-based alternative (with case-insensitive comparisons), if such an option exists in Oracle.
当您需要进行不区分大小写的比较时，硬着头皮处理 ANSI 连接。或者研究基于语言环境的替代方案（使用不区分大小写的比较），如果 Oracle 中存在这样的选项。
Fundamentally, though, you should consign the old '(+)' outer join notation to the trash can. When you have to modify an SQL statement, remove the old (obsolescent) notation and use the ANSI join notation instead.
但是，从根本上说，您应该将旧的“(+)”外连接符号委托给垃圾桶。当您必须修改 SQL 语句时，请删除旧的（过时的）表示法并改用 ANSI 连接表示法。
A comment asks 'how can this be converted to ANSI'?
一条评论询问“如何将其转换为 ANSI”？
You rewrite the FROM clause as well as the WHERE clause - often moving join conditions from the WHERE clause to the ON conditions in the FROM clause.
您重写 FROM 子句以及 WHERE 子句 - 通常将连接条件从 WHERE 子句移动到 FROM 子句中的 ON 条件。
SELECT a.*, b.*
FROM a LEFT OUTER JOIN b ON UPPER(a.name) = UPPER(b.lname)
Another comment asks 'how to extend the join to three tables'?
另一条评论询问“如何将连接扩展到三个表”？
SELECT a.*, b.*
FROM a
LEFT OUTER JOIN b ON UPPER(a.name) = UPPER(b.lname)
LEFT OUTER JOIN c ON on a.first = c.first
回答by vijay
AND UPPER(a.name) = UPPER(b.lname (+))??????????????
Works. I tested it. It is working fine.
作品。我测试了它。它工作正常。
