Left Outer Join using + sign in Oracle 11g

user_824210 0
Viewed 577857

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
oracle11g sql
7 Answers

I saw some contradictions in the answers above, I just tried the following on Oracle 12c and the following is correct :

LEFT OUTER JOIN

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

RIGHT OUTER JOIN

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

LEFT OUTER JOIN

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

RIGHT OUTER JOIN

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

user_67015420 
You can see answers from previous posts
However I added little more information 
    create table r2020 (id int, name varchar2(50),rank number);
    insert into r2020 values (101,'Rob Rama',1);
    insert into r2020 values (102,'Ken Krishna',3);
    insert into r2020 values (108,'Ray Rama',2);
    insert into r2020 values (109,'Kat Krishna',4);
    
    create table r2021 (id int, name varchar2(50),rank number);
    insert into r2021 values (102,'Ken Krishna',1); 
    insert into r2021 values (103,'Tom Talla',2);
    insert into r2021 values (108,'Ray Rama',2); 
    
    --LEFT OUTER JOIN  
        --oracle notation
        select * from r2020 r1, r2021 r2
        where  r1.id  = r2.id (+) 
        order by r1.id;
        
        --ANSI notation
        select * from r2020 r1
        left outer join r2021 r2 on  r1.id = r2.id  
        order by r1.id;
        --OUT PUT
        NAME        ID RANK  NAME_1     ID_1    RANK_1
        ----        -- ---- ----        ----   ------
        Rob Rama    101 1   (null)      (null)  (null)
        Ken Krishna 102 3   Ken Krishna 102     1
        Ray Rama    108 2   Ray Rama    108     2
        Kat Krishna 109 4   (null)      (null)  (null)
    
    --RIGHT OUTER JOIN  
        --oracle notation
        select * from r2020 r1, r2021 r2
        where  r1.id (+)  = r2.id 
        order by r1.id;
        
        --ANSI notation
        select * from r2020 r1
        right outer join r2021 r2 on  r1.id = r2.id  
        order by r1.id;
        --OUT PUT
        NAME        ID    RANK    NAME_1      ID_1    RANK_1
        ----        --    ----    ----        ----   ------
        Ken Krishna 102     3     Ken Krishna 102    1
        Ray Rama    108     2     Ray Rama    108    2
        (null)      (null) (null) Tom Talla   103    2
        
        
    --<b>MULTIPLE COLUMNS IN JOIN CONDITION</b>
    --LEFT OUTER JOIN  
        --oracle notation
        select * from r2020 r1, r2021 r2
        where  r1.id  = r2.id (+) and 
               r1.rank  = r2.rank (+)
        order by r1.id;
        
        --ANSI notation
        select * from r2020 r1
        left outer join r2021 r2 on  r1.id = r2.id and 
                                     r1.rank  = r2.rank 
        order by r1.id;
        --OUT PUT
        NAME        ID RANK  NAME_1     ID_1    RANK_1
        ----        -- ---- ----        ----   ------
        Rob Rama    101 1   (null)      (null)  (null)
        Ken Krishna 102 3   (null)      (null)  (null)
        Ray Rama    108 2   Ray Rama    108     2
        Kat Krishna 109 4   (null)      (null)  (null)
    
    --RIGHT OUTER JOIN  
        --oracle notation
        select * from r2020 r1, r2021 r2
        where  r1.id (+)  = r2.id and
               r1.rank(+)  = r2.rank 
        order by r1.id;
        
        --ANSI notation
        select * from r2020 r1
        right outer join r2021 r2 on  r1.id = r2.id and
                                      r1.rank  = r2.rank 
        order by r1.id;
        --OUT PUT
        NAME      ID     RANK   NAME_1       ID_1  RANK_1
        ----      --     ----   ----         ----  ------
        (null)    (null) (null) Ken Krishna  102   1
        Ray Rama  108     2     Ray Rama     108   2
        (null)    (null) (null) Tom Talla    103   2
user_8490300

To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause

user_186816850
Related