Left Outer Join using + sign in Oracle 11g

Question

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

You should avoid using the '(+)' notation and upgrade the queries to use explicit joins. — Jonathan Leffler, Jul 02 '11 at 20:50
@JonathanLeffler 100% agree. The problem is that I work with people who don't want to swith to the standard notation. I write new query with standard notation but I'll be shoot in a minute if a modified an old one. — Luc M, Aug 07 '12 at 14:24
@JonathanLeffler I would agree, unless you are using Oracle. Oracle as it stands currently does not handle the ansi syntax as well as the (+) operator internally. Though they do recommend using the ansi syntax :) http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm — A myth, Mar 19 '13 at 17:32
@Amyth Sorry for this way outdated comment, but I came to this question from a search. As of myself, I understand Oracle's recommendations the exact opposite way. From your link: _"**Oracle recommends that you use the FROM clause OUTER JOIN syntax** rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax ..."_ — Sylvain Leroux, Oct 23 '14 at 09:40
Sorry for the outdated response as well :), but what Oracle says, and how its stats work to optimize the queries are two different things, and it might be that Oracle has changed its stance as well with advances to its internal optimizers — A myth, Jan 28 '15 at 15:22

score 197 · Accepted Answer · edited May 23 '17 at 12:26

197

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.

edited May 23 '17 at 12:26

Community

1
1

answered Jul 02 '11 at 20:02

Wiseguy

20,522
8
65
81

Could you add examples for non-explicit `FROM A, B` situations? Let's say, in `FROM A, B WHERE a.x = b.x (+)` and `FROM A, B WHERE b.x (+) = a.x`, is the former a right and the latter a left join in your terminology (because of the location of the +), or are they both right joins because `B` is on the right? – Kerrek SB Jul 02 '11 at 20:34
@Kerrek SB The ones in the original question are, as are the ones at the link in my answer above. Would you like additional ones? – Wiseguy Jul 02 '11 at 20:35
3

Right, I get it now -- the JOIN is created implicitly by the presence of the (+). Cool. – Kerrek SB Jul 02 '11 at 20:37
@Kerrek SB Exactly. I just updated my answer. Hopefully that's helpful. – Wiseguy Jul 02 '11 at 20:42
Got it, cheers. I think this implicit syntax should be deprecated in favour of explicit JOINs, though, don't you think? – Kerrek SB Jul 02 '11 at 20:43
@Kerrek SB It's not _deprecated_, per se, but it is no longer recommended. – Wiseguy Jul 02 '11 at 20:47
You're right, I got that wrong. I'll just remove my answer, since yours is better. @Mike: Just stick with this one, sorry for the confusion. – Kerrek SB Jul 02 '11 at 20:50
@Kerrek SB: If the criteria is determined by placement of + sign, then `SELECT P.Part_Id, S.Supplier_Name FROM Supplier S, Part P WHERE P.Supplier_Id = S.Supplier_Id (+)` would be Right Outer Join, right? But Wiseguy's example is still confusing? – Mike Jul 02 '11 at 20:54
@Mike: No, sorry, I got it the wrong way round. The + is on the other side, so you got "Part LEFT Supplier", and you show the unmatched *parts*. I was confused! – Kerrek SB Jul 02 '11 at 20:55
@Wiseguy: Why below one is Left Outer Join? `SELECT * FROM A, B WHERE A.column = B.column(+) ` when the + is on right side? – Mike Jul 02 '11 at 20:56
@Kerrek SB, @Wiseguy: So, can I blindly assume that if the (+) is on the right, it's a LEFT JOIN & If (+) is on the left, it's a RIGHT JOIN. & that `SELECT P.Part_Id, S.Supplier_Name FROM Supplier S, Part P WHERE P.Supplier_Id = S.Supplier_Id (+) ` is an example of Left Outer Join? – Mike Jul 02 '11 at 20:59
2

@Mike: That's the way the + syntax works. It means "optional", so read it like "List all parts, optionally make the supplier match". – Kerrek SB Jul 02 '11 at 20:59
2

@Mike: As long as you know what you're selecting, it doesn't really matter how you call it. But do yourself a favour and use the idiomatic `JOIN` syntax instead! Then there is no room for confusion. – Kerrek SB Jul 02 '11 at 21:01
@Kerrek Right. That's a good way to put it. @Mike **LEFT** JOIN means the **left** table will have all records, and the right table may or may not match. (Thus the `(+)` is on the right.) – Wiseguy Jul 02 '11 at 21:03
@Kerrek SB: I'm more interested to know whether its left or right as I know that + is meant for optional. What's confusing for me is whether its left or right outer join & whether that depends on the order of tables in the FROM clause. But it seems like it does not depend on the order & we can blindly say that its a left join if + is on right & vice-versa, right? – Mike Jul 02 '11 at 21:03
@Mike Correct. Order in `FROM a, b, c` does _not_ matter. What matters is the placement of `(+)`. That's it. But yes, even better, don't use this syntax anyway. See why it's confusing? :-) – Wiseguy Jul 02 '11 at 21:04
@Wiseguy: Not that I'm criticizing any one's answer here, but I wanna know if its written somewhere in oracle documentation that using + on right side is left join & vice-versa. And I know I should use ANSI syntacx but there is lot of legacy code which is using + signs & I wanted to understand the correct terminology behind it. – Mike Jul 02 '11 at 21:07
@Mike It's only a conceptual thing, since `A LEFT JOIN B` is the same as `B RIGHT JOIN A`, so it's always going to be both. :-) Just depends how you think about it. For Oracle docs, see "Outer Joins" section here: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm – Wiseguy Jul 02 '11 at 21:12
@Mike If it helps, you can always just switch `A.col(+) = B.col` to `B.col = A.col(+)` so you never have to worry about RIGHT vs LEFT because they'll all be LEFT. :-) The query will work identically either way, but maybe that'll be easier to think about. – Wiseguy Jul 02 '11 at 21:17
@Wiseguy: I'm not just comparing A LEFT JOIN B vs B RIGHT JOIN A. In future, if I ever see a + sign on right side, I can simply assume that its a left join & vice-versa, right? That's what I wanna know.. – Mike Jul 02 '11 at 21:19
@Mike Yes, that is correct. If it helps, you can swap conditions around so that the `(+)`s are all on the right side, then every join is LEFT, so you don't have to worry about thinking that some are RIGHT and some are LEFT. – Wiseguy Jul 02 '11 at 21:24
how about (-) notation .. what's the meaning? – Ras Rass Jan 10 '17 at 07:46
@user1425503 I am not familiar with that. Can you provide an example? This is best asked as a new question, since not many people will see it in the comments here. – Wiseguy Jan 12 '17 at 00:38
Hoe to write full outer join implicitly? – Tom J Muthirenthi Mar 01 '17 at 12:56
1

@TomJMuthirenthi Without using explicit `FULL OUTER JOIN` syntax, you'll need to `UNION [ALL]` two result sets together: one for A = B(+) and one for B = A(+). Example in [this question](http://stackoverflow.com/q/4147702/185544). – Wiseguy Mar 01 '17 at 15:34
2

The "(+)" goes on the columns of the table that generates subrows of nulls. – philipxy Jun 08 '17 at 06:18
Right Outer Join (+) has to be on the right side.SELECT * FROM A, B WHERE A.column(+) = B.column – Rupasa Sushma Aug 17 '20 at 09:37

score 0 · Answer 2 · answered Mar 26 '21 at 14:33

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

score -3 · Answer 3 · edited Sep 30 '14 at 21:25

There is some incorrect information in this thread. I copied and pasted the incorrect information:

LEFT OUTER JOIN

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

RIGHT OUTER JOIN

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

The above is WRONG!!!!! It's reversed. How I determined it's incorrect is from the following book:

Oracle OCP Introduction to Oracle 9i: SQL Exam Guide. Page 115 Table 3-1 has a good summary on this. I could not figure why my converted SQL was not working properly until I went old school and looked in a printed book!

Here is the summary from this book, copied line by line:

Oracle outer Join Syntax:

from tab_a a, tab_b b,                                       
where a.col_1 + = b.col_1

ANSI/ISO Equivalent:

from tab_a a left outer join  
tab_b b on a.col_1 = b.col_1

Notice here that it's the reverse of what is posted above. I suppose it's possible for this book to have errata, however I trust this book more so than what is in this thread. It's an exam guide for crying out loud...

This contradicts what I linked to in my answer, which is an excerpt from Oracle Database 10g SQL (Osborne ORACLE Press Series) 1st edition (February 20, 2004), stating: _"In a left outer join, the outer join operator is actually on the right of the equality operator."_ Here is [a demo of your example](http://sqlfiddle.com/#!4/8daff0/10). The results of `a.col_1(+) = b.col_1` match RIGHT JOIN, not LEFT JOIN. — Wiseguy, Sep 30 '14 at 21:26

Left Outer Join using + sign in Oracle 11g

3 Answers3

RIGHT vs LEFT

Types of JOIN

Linked

Related