在Oracle 11g中使用+登录进行左外部联接 [英] Left Outer Join using + sign in Oracle 11g

问题描述

有人可以告诉我以下两个查询是左外部连接"还是右外部连接"的示例?

Can any one tell me whether below 2 queries are an example of Left Outer Join or Right Outer Join??

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

推荐答案

TableA LEFT OUTER JOIN TableB等效于TableB RIGHT OUTER JOIN Table A.

在Oracle中，(+)表示JOIN中的可选"表.因此，在您的第一个查询中，它是一个P LEFT OUTER JOIN S.在第二个查询中，它是S RIGHT OUTER JOIN P. 它们在功能上是等效的.

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.

在术语中，RIGHT或LEFT指定连接的哪一侧总是有记录，而另一侧可能为null.因此，在P LEFT OUTER JOIN S中，P总是有一条记录，因为它在LEFT上，但是S可以为空.

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, P will always have a record because it's on the LEFT, but S could be null.

有关其他说明，请参见来自java2s.com的此示例.

See this example from java2s.com for additional explanation.

为澄清起见，我想我是说术语并不重要，因为它仅用于可视化.重要的是您了解其工作原理.

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与LEFT的关系，我已经感到有些困惑.

I've seen some confusion about what matters in determining RIGHT vs LEFT in implicit join syntax.

左外联接

SELECT *
FROM A, B
WHERE A.column = B.column(+)

右外加入

SELECT *
FROM A, B
WHERE B.column(+) = A.column

我所做的只是交换WHERE子句中的术语，但是它们在功能上仍然等效. (有关此问题的更多信息，请参见我的答案的较高部分.)(+)的位置确定为RIGHT或LEFT. (具体来说，如果(+)在右侧，则是一个左联接.如果(+)在左侧，则是一个右联接.)

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.)

JOIN的两种样式是隐式JOINs 和显式JOINs .它们是编写JOIN的不同样式，但是在功能上是等效的.

The two styles of JOIN are implicit JOINs and explicit JOINs. They are different styles of writing JOINs, but they are functionally equivalent.

请参见此SO问题.

隐式联接只需将所有表一起列出.连接条件在WHERE子句中指定.

Implicit JOINs simply list all tables together. The join conditions are specified in a WHERE clause.

隐式加入

SELECT *
FROM A, B
WHERE A.column = B.column(+)

显式联接将联接条件与特定表的包含而不是在WHERE子句中相关联.

Explicit JOINs associate join conditions with a specific table's inclusion instead of in a WHERE clause.

显式加入

SELECT *
FROM A
LEFT OUTER JOIN B ON A.column = B.column

这些 隐式JOIN可能更难以阅读和理解，并且由于在其他WHERE条件中混合了联接条件，因此它们也有一些限制.因此，通常建议不要使用隐式JOIN，而应使用显式语法.

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.

这篇关于在Oracle 11g中使用+登录进行左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！