在 Oracle 11g 中使用 + 登录的左外连接 [英] Left Outer Join using + sign in Oracle 11g

问题描述

谁能告诉我以下 2 个查询是左外连接还是右外连接的示例??

表格部分:姓名为空?类型PART_ID 非空 VARCHAR2(4)供应商_ID VARCHAR2(4)PART_ID 供应商_IDP1 S1P2 S2P3P4表供应商:姓名为空?类型SUPPLIER_ID 非空 VARCHAR2(4)SUPPLIER_NAME NOT NULL VARCHAR2(20)SUPPLIER_ID SUPPLIER_NAMES1 供应商#1S2供应商#2S3供应商#3

显示所有零件，无论是否有供应商供应它们:

<前>SELECT P.Part_Id, S.Supplier_Name来自 P 部分，供应商 SWHERE P.Supplier_Id = S.Supplier_Id (+)SELECT P.Part_Id, S.Supplier_Name来自 P 部分，供应商 SWHERE 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.它们在功能上是等效的.

在术语中，RIGHT 或 LEFT 指定联接的哪一侧始终有记录，另一侧可能为空.所以在 P LEFT OUTER JOIN S 中，P 总会有一个记录，因为它在 LEFT 上，但是 S> 可以为空.

请参阅 java2s.com 中的此示例 以获取更多说明.

<小时>

澄清一下，我想我是说术语无关紧要，因为它只是帮助形象化.重要的是您了解它的工作原理.

<小时>

右与左

对于在隐式连接语法中确定 RIGHT 和 LEFT 的重要性，我看到了一些混淆.

左外连接

SELECT *从 A, BWHERE A.column = B.column(+)

右外连接

SELECT *从 A, BWHERE B.column(+) = A.column

我所做的只是交换 WHERE 子句中术语的两边，但它们在功能上仍然是等效的.(有关更多信息，请参阅我的回答中的更高部分.) (+) 的位置决定了 RIGHT 或 LEFT.(具体来说，如果 (+) 在右侧，则为 LEFT JOIN.如果 (+) 在左侧，则为 RIGHT JOIN.)

<小时>

JOIN 的类型

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

参见这个问题.

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

隐式连接

SELECT *从 A, BWHERE A.column = B.column(+)

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

显式连接

SELECT *从 AA.column = B.column 上的左外连接 B

这些隐式 JOIN 可能更难阅读和理解，而且它们也有一些限制，因为连接条件与其他 WHERE 条件混合在一起.因此，通常不建议使用隐式 JOIN，而建议使用显式语法.

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 is equivalent to 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.

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.

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 vs LEFT

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

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

---

Types of JOIN

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

See this SO question.

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

Implicit JOIN

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

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

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.

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