如何使用ORACLE JOIN(+)查找LEFT OUTER JOIN或RIGHT OUTER JOIN [英] How to find LEFT OUTER JOIN or RIGHT OUTER JOIN with ORACLE JOIN (+)

问题描述

我对使用Oracle联接(+)符号正确找到左外部联接和右外部联接感到困惑.检查此与

I am confused with finding left outer join and right outer join properly with Oracle join (+) sign. Check this vs this. I feel both contradict. What I understand is, First link says if the (+) sign is in right hand side, it will be Right Outer Join.

在第二个链接中，我的理解是完全错误的.

Whereas with second link, my understanding is totally wrong.

请举例说明如何正确找到左右外部连接?

Please clarify how to find the right and left outer join properly with an example?

推荐答案

左外部联接仅意味着您要返回联接左侧的所有表，以及右侧表中的所有匹配行

Left outer join just means that you want to return all of the table on the left hand side of the join, and any matching rows from the table on the right.

在旧式Oracle语法中，其为:where t1.col1 = t2.col1 (+) 在ANSI语法中，应为:from t1 left outer join t2 on (t1.col1 = t2.col1)

In old-style Oracle syntax, that would be: where t1.col1 = t2.col1 (+) In ANSI syntax, that would be: from t1 left outer join t2 on (t1.col1 = t2.col1)

右外部联接意味着您想在联接的右侧返回所有表，并在左侧返回表中所有匹配的行.

Right outer join means that you want to return all of the table on the right hand side of the join, and any matching rows from the table on the left.

在旧式Oracle语法中，应为:where t2.col1 (+) = t1.col1 用ANSI语法，应为:from t2 right outer join t1 on (t2.col1 = t1.col1)

In old-style Oracle syntax, that would be: where t2.col1 (+) = t1.col1 In ANSI syntax, that would be: from t2 right outer join t1 on (t2.col1 = t1.col1)

您当然会发现，只需反转表的顺序就可以将右外部联接转换为左外部联接.大多数外部联接都是左联接，可能是因为更容易想到我想要所有第一个表以及该另一个表中的所有匹配行"，而不是相反. YMMV，当然！

You will, of course, have spotted that you can turn a right outer join into a left outer join simply by reversing the order of the tables. Most outer joins are left ones, probably because it's easier to think of "I want all of this first table, and any matching rows from this other table" rather than the other way round. YMMV, of course!

ETA以下示例:

左外部联接:

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from   t1, t2
where  t1.col1 = t2.col1 (+)
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30   

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from t1 left outer join t2 on (t1.col1 = t2.col1)
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30   

右外部联接:

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from   t1, t2
where t2.col1 (+) = t1.col1
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30   

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from t2 right outer join t1 on (t2.col1 = t1.col1)
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30   

这篇关于如何使用ORACLE JOIN(+)查找LEFT OUTER JOIN或RIGHT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！