Oracle左语法连接三个或更多表 [英] Oracle syntax left joins three or more tables
问题描述
我正在尝试将旧的oracle Left Join语法缠住我的头. 有两个表就可以了:
FROM A, B
WHERE
A.Col = B.Col (+)
(让我们将此查询称为Q0)
这很容易理解，例如与此Venn 关系图
但是当我们向表中添加第三张或更多张表时，就是我的大脑想要关闭:
FROM A,B,C
WHERE
A.C = B.C (+) AND
C.C = A.C (+)
(让我们称之为Q1)
应该等于:(我刚刚更改了左侧加入条件的位置)
FROM A,B,C
WHERE
C.C = A.C (+) AND
A.C = B.C (+)
(我们将此称为第二季度)
这是我对这些查询的看法，我的问题是我是否正确理解了这些问题:
据我所知，一种解释多于2个表的左联接的方法是将其视为一串左联接，其中一个是另一个的输入，这是正确的吗?
因此，在Q1中，我们有两个左联接，第一个是A与B左联接，其中A是左表.剩下的第二个连接对我来说开始变得棘手.起初我以为是表C左连接表A，其中表C是左表.但这似乎是不正确的，应该将其视为C左连接与第一个左连接的结果，其中C是左表. 这正确吗?
但是我如何在第二季度应用这个链式原理"?
我尝试从Q1复制推理:第一个联接是C左联接，而A是左联接.第二个联接是A与第一个联接的结果之间的左联接，其中A是左表.但这是没有道理的，第二个左联接的联接条件在A列和B列之间，并且第一个左联接的结果中没有B列.
出于好奇，我尝试在第二个联接中切换左表和右表，这是第一个联接的结果将作为第二个左联接中的左表，并与B左联接.看来这可行，结果与Q1相同.但是我不知道如何解释它为什么起作用.
因此，如果任何人在使用oracle语法时可以建议一种推理方法来对三个或多个表进行通用应用，将不胜感激. 如果这样的通用方法由将oracle语法转换为ANSI语法的通用方法组成，它也可以做到，因为我可以很容易地理解这一点.
您可以级联方式查看它.但是，关键是要查找在同一查询中左右连接的那些表.在这种情况下，顺序是不同的:首先应用正确连接表的条件.我希望下面的图可以对此有所启发:
您还可以通过查看查询的执行计划来检查这些联接的顺序:
第一季度:
select a.c a, b.c b, c.c c from a, b, c where a.c = b.c (+) and
c.c = a.c (+)
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN OUTER | | 4 | 2168K| 2168K| 805K (0)|
|* 2 | HASH JOIN OUTER | | 4 | 2616K| 2616K| 981K (0)|
| 3 | TABLE ACCESS FULL| C | 4 | | | |
| 4 | TABLE ACCESS FULL| A | 4 | | | |
| 5 | TABLE ACCESS FULL | B | 4 | | | |
------------------------------------------------------------------------
第二季度:
select a.c a, b.c b, c.c c from a, b, c where c.c = a.c (+) and
a.c = b.c (+)
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN OUTER | | 4 | 2168K| 2168K| 801K (0)|
|* 2 | HASH JOIN OUTER | | 4 | 2616K| 2616K| 983K (0)|
| 3 | TABLE ACCESS FULL| C | 4 | | | |
| 4 | TABLE ACCESS FULL| A | 4 | | | |
| 5 | TABLE ACCESS FULL | B | 4 | | | |
------------------------------------------------------------------------
I am trying to wrap my head around the old oracle Left Join syntax. It's fine with two tables:
FROM A, B
WHERE
A.Col = B.Col (+)
(Lets call this query, Q0)
This is easy enough to understand e.g. with this Venn diagram
But when we add a third table or more to the mix that's when my brain wants to shut down:
FROM A,B,C
WHERE
A.C = B.C (+) AND
C.C = A.C (+)
(Lets call this Q1)
Which should be equal to: (I've just changed places on the left join conditions)
FROM A,B,C
WHERE
C.C = A.C (+) AND
A.C = B.C (+)
(Lets call this one Q2)
So here is my take on these queries, my question here is if I have understood it correctly:
To my understanding a way to interpret left joins of more than 2 tables is to see it as a chain of left joins, where one is the input to the other, is this correct?
So in Q1 we have two left joins, the first is A Left Joined with B where A is the left table. The second left join is where it starts to get tricky for me. At first I thought it was table C left joined with table A where C was the left table. But this seem to be incorrect, rather it should be seen as C left joined with the result of the first left join, where C is the left table. Is this correct?
But how do I apply this "chain-principle" in Q2?
I tried copying the reasoning from Q1: the first join is a C left joined with A where C is the left table. The second join is a left join between A and the result of the first join, where A is the left table. But this doesn't make sense, the join condition of the second left join is between A and B columns, and there is no B column in the result from the first left join.
Out of curiosity+ I tried switching left and right tables in the second join, that is the result of the first join would instead act as the left table in the second left join, left joined with B. And it seems that this works, the result becomes the same as as Q1. But I have no idea how to explain why that works.
So if anyone could suggest a way of reasoning to apply generically for three or more tables when using the oracle syntax it would be greatly appreciated. It would also do if such a generic method consisted of a general way to translate an oracle syntax to ANSI syntax, since I can understand that much easier.
You can see it in a cascading way. However, the key is to look for those tables which are left and right joined within the same query. In this case, the order is different: the condition where the table is right joined is applied first. I hope the following diagram will shed some light on this:
You can also check the order of these joins by looking at the execution plan of the query:
For Q1:
select a.c a, b.c b, c.c c from a, b, c where a.c = b.c (+) and
c.c = a.c (+)
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN OUTER | | 4 | 2168K| 2168K| 805K (0)|
|* 2 | HASH JOIN OUTER | | 4 | 2616K| 2616K| 981K (0)|
| 3 | TABLE ACCESS FULL| C | 4 | | | |
| 4 | TABLE ACCESS FULL| A | 4 | | | |
| 5 | TABLE ACCESS FULL | B | 4 | | | |
------------------------------------------------------------------------
For Q2:
select a.c a, b.c b, c.c c from a, b, c where c.c = a.c (+) and
a.c = b.c (+)
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN OUTER | | 4 | 2168K| 2168K| 801K (0)|
|* 2 | HASH JOIN OUTER | | 4 | 2616K| 2616K| 983K (0)|
| 3 | TABLE ACCESS FULL| C | 4 | | | |
| 4 | TABLE ACCESS FULL| A | 4 | | | |
| 5 | TABLE ACCESS FULL | B | 4 | | | |
------------------------------------------------------------------------
这篇关于Oracle左语法连接三个或更多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
