SQL 在 Oracle 11g 中使用 + 登录的左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6559261/
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
Left Outer Join using + sign in Oracle 11g
提问by Mike
Can any one tell me whether below 2 queries are an example of Left Outer Join or Right Outer Join??
谁能告诉我以下 2 个查询是左外连接还是右外连接的示例？
Table Part:
Name Null? Type
PART_ID NOT NULL VARCHAR2(4)
SUPPLIER_ID VARCHAR2(4)
PART_ID SUPPLIER_ID
P1 S1
P2 S2
P3
P4
Table Supplier:
Name Null? Type
SUPPLIER_ID NOT NULL VARCHAR2(4)
SUPPLIER_NAME NOT NULL VARCHAR2(20)
SUPPLIER_ID SUPPLIER_NAME
S1 Supplier#1
S2 Supplier#2
S3 Supplier#3
Display all the parts irrespective of whether any supplier supplies them or not:
显示所有零件，无论是否有供应商供应它们：
SELECT P.Part_Id, S.Supplier_Name FROM Part P, Supplier S WHERE P.Supplier_Id = S.Supplier_Id (+) SELECT P.Part_Id, S.Supplier_Name FROM Part P, Supplier S WHERE S.Supplier_Id (+) = P.Supplier_Id
回答by Wiseguy
TableA LEFT OUTER JOIN TableBis equivalent to TableB RIGHT OUTER JOIN Table A.
TableA LEFT OUTER JOIN TableB相当于TableB RIGHT OUTER JOIN Table A。
In Oracle, (+)denotes the "optional" table in the JOIN. So in your first query, it's a P LEFT OUTER JOIN S. In your second query, it's S RIGHT OUTER JOIN P. They're functionally equivalent.
在 Oracle 中，(+)表示 JOIN 中的“可选”表。所以在你的第一个查询中，它是一个P LEFT OUTER JOIN S. 在您的第二个查询中，它是S RIGHT OUTER JOIN P. 它们在功能上是等效的。
In the terminology, RIGHT or LEFT specify which side of the join always has a record, and the other side might be null. So in a P LEFT OUTER JOIN S, Pwill always have a record because it's on the LEFT, but Scould be null.
在术语中，RIGHT 或 LEFT 指定联接的哪一侧始终有记录，另一侧可能为空。所以在 a 中P LEFT OUTER JOIN S，P将始终有一个记录，因为它在 上LEFT，但S可能为空。
See this example from java2s.comfor additional explanation.
有关其他说明，请参阅java2s.com 中的此示例。
To clarify, I guess I'm saying that terminology doesn't matter, as it's only there to help visualize. What matters is that you understand the concept of how it works.
澄清一下，我想我是说术语无关紧要，因为它只是帮助形象化。重要的是你理解它是如何工作的概念。
RIGHT vs LEFT
右与左
I've seen some confusion about what matters in determining RIGHT vs LEFT in implicit join syntax.
我已经看到一些关于在隐式连接语法中确定 RIGHT 与 LEFT 的重要性的混淆。
LEFT OUTER JOIN
左外连接
SELECT *
FROM A, B
WHERE A.column = B.column(+)
RIGHT OUTER JOIN
右外连接
SELECT *
FROM A, B
WHERE B.column(+) = A.column
All I did is swap sides of the terms in the WHERE clause, but they're still functionally equivalent. (See higher up in my answer for more info about that.) The placement of the (+)determines RIGHT or LEFT. (Specifically, if the (+)is on the right, it's a LEFT JOIN. If (+)is on the left, it's a RIGHT JOIN.)
我所做的只是交换 WHERE 子句中术语的两边，但它们在功能上仍然是等效的。（有关更多信息，请参阅我的回答中的更高部分。） 的位置(+)决定了 RIGHT 或 LEFT。（具体来说，如果 位于(+)右侧，则为 LEFT JOIN。如果(+)位于左侧，则为 RIGHT JOIN。）
Types of JOIN
JOIN 的类型
The two styles of JOIN are implicit JOINsand explicit JOINs. They are different styles of writing JOINs, but they are functionally equivalent.
JOIN 的两种样式是隐式 JOIN和显式 JOIN。它们是编写 JOIN 的不同风格，但它们在功能上是等效的。
See this SO question.
看到这个问题。
Implicit JOINssimply list all tables together. The join conditions are specified in a WHERE clause.
隐式 JOIN只是将所有表一起列出。连接条件在 WHERE 子句中指定。
Implicit JOIN
隐式连接
SELECT *
FROM A, B
WHERE A.column = B.column(+)
Explicit JOINsassociate join conditions with a specific table's inclusion instead of in a WHERE clause.
显式 JOIN 将连接条件与特定表的包含相关联，而不是在 WHERE 子句中。
Explicit JOIN
显式连接
SELECT *
FROM A
LEFT OUTER JOIN B ON A.column = B.column
These Implicit JOINs can be more difficult to read and comprehend, and they also have a few limitations since the join conditions are mixed in other WHERE conditions. As such, implicit JOINs are generally recommended against in favor of explicit syntax.
这些隐式 JOIN 可能更难阅读和理解，而且它们也有一些限制，因为连接条件与其他 WHERE 条件混合在一起。因此，通常建议不要使用隐式 JOIN 来支持显式语法。
回答by Rahul
Those two queries are performing OUTER JOIN. See below
这两个查询正在执行OUTER JOIN。见下文
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
The (+) operator can appear only in the WHERE clause or, in the context of left- correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:
-- The following statement is not valid: SELECT employee_id, manager_id FROM employees WHERE employees.manager_id(+) = employees.employee_id;However, the following self join is valid:
SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.
Oracle 建议您使用 FROM 子句 OUTER JOIN 语法而不是 Oracle 连接运算符。使用 Oracle 连接运算符 (+) 的外连接查询受以下规则和限制的约束，这些规则和限制不适用于 FROM 子句 OUTER JOIN 语法：
您不能在还包含 FROM 子句连接语法的查询块中指定 (+) 运算符。
(+) 运算符只能出现在 WHERE 子句中，或者出现在 FROM 子句中的左相关上下文中（当指定 TABLE 子句时），并且只能应用于表或视图的列。
如果 A 和 B 由多个连接条件连接，则您必须在所有这些条件中使用 (+) 运算符。如果不这样做，则 Oracle 数据库将仅返回由简单联接产生的行，但不会出现警告或错误提示您没有外部联接的结果。
如果在外部查询中指定一个表而在内部查询中指定另一个表，则 (+) 运算符不会产生外部连接。
您不能使用 (+) 运算符将表与自身外部连接，尽管自连接是有效的。例如，以下语句无效：
-- The following statement is not valid: SELECT employee_id, manager_id FROM employees WHERE employees.manager_id(+) = employees.employee_id;但是，以下自联接是有效的：
SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;(+) 运算符只能应用于列，不能应用于任意表达式。但是，任意表达式可以包含一个或多个用 (+) 运算符标记的列。
包含 (+) 运算符的 WHERE 条件不能与使用 OR 逻辑运算符的另一个条件组合。
WHERE 条件不能使用 IN 比较条件将标有 (+) 运算符的列与表达式进行比较。
如果 WHERE 子句包含将表 B 中的列与常量进行比较的条件，则必须将 (+) 运算符应用于该列，以便 Oracle 返回表 A 中已为该列生成空值的行。否则 Oracle 只返回简单连接的结果。
在执行两对以上表的外连接的查询中，单个表可以是仅另一个表的空生成表。因此，不能将 (+) 运算符应用于 A 和 B 的连接条件以及 B 和 C 的连接条件中的 B 列。有关外部连接的语法，请参阅 SELECT。
Taken from http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm
取自http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm
回答by Charles
I saw some contradictions in the answers above, I just tried the following on Oracle 12c and the following is correct :
我在上面的答案中看到了一些矛盾，我只是在 Oracle 12c 上尝试了以下内容，以下是正确的：
LEFT OUTER JOIN
左外连接
SELECT *
FROM A, B
WHERE A.column = B.column(+)
RIGHT OUTER JOIN
右外连接
SELECT *
FROM A, B
WHERE B.column(+) = A.column
回答by somedude
There is some incorrect information in this thread. I copied and pasted the incorrect information:
此线程中有一些不正确的信息。我复制并粘贴了不正确的信息：
LEFT OUTER JOIN
SELECT * FROM A, B WHERE A.column = B.column(+)RIGHT OUTER JOIN
SELECT * FROM A, B WHERE B.column(+) = A.column
左外连接
SELECT * FROM A, B WHERE A.column = B.column(+)右外连接
SELECT * FROM A, B WHERE B.column(+) = A.column
The above is WRONG!!!!! It's reversed. How I determined it's incorrect is from the following book:
楼上说错了！！！！！这是相反的。我如何确定它不正确是从以下书中：
Oracle OCP Introduction to Oracle 9i: SQL Exam Guide. Page 115 Table 3-1 has a good summary on this. I could not figure why my converted SQL was not working properly until I went old school and looked in a printed book!
Oracle OCP Oracle 9i 简介：SQL 考试指南。第 115 页 表 3-1 对此做了很好的总结。我不明白为什么我转换后的 SQL 不能正常工作，直到我回到老学校并查看印刷书籍！
Here is the summary from this book, copied line by line:
这是本书的摘要，逐行复制：
Oracle outer Join Syntax:
Oracle 外连接语法：
from tab_a a, tab_b b,
where a.col_1 + = b.col_1
ANSI/ISO Equivalent:
ANSI/ISO 等效标准：
from tab_a a left outer join
tab_b b on a.col_1 = b.col_1
Notice here that it's the reverse of what is posted above. I suppose it's possible for this book to have errata, however I trust this book more so than what is in this thread. It's an exam guide for crying out loud...
请注意，这里与上面发布的内容相反。我想这本书可能有勘误表，但是我更相信这本书而不是这个线程中的内容。这是一本大声哭的考试指南......
