Left Outer Join using + sign in Oracle 11g

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.

Those two queries are performing OUTER JOIN. See below

> 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: > > - You cannot specify the (+) operator in a query block that also > contains FROM clause join syntax. > > - The (+) operator can appear only in the WHERE clause or, in the > context of left- correlation (when specifying the TABLE clause) in the > FROM clause, and can be applied only to a column of a table or view. > > - If A and B are joined by multiple join conditions, then you must use > the (+) operator in all of these conditions. If you do not, then > Oracle Database will return only the rows resulting from a simple > join, but without a warning or error to advise you that you do not > have the results of an outer join. > > - The (+) operator does not produce an outer join if you specify one > table in the outer query and the other table in an inner query. > > - You cannot use the (+) operator to outer-join a table to itself, > although self joins are valid. For example, the following statement > is not valid: > > -- The following statement is not valid: > SELECT employee_id, manager_id > FROM employees > WHERE employees.manager_id(+) = employees.employee_id; > > However, the following self join is valid: > > SELECT e1.employee_id, e1.manager_id, e2.employee_id > FROM employees e1, employees e2 > WHERE e1.manager_id(+) = e2.employee_id > ORDER BY e1.employee_id, e1.manager_id, e2.employee_id; > > - The (+) operator can be applied only to a column, not to an arbitrary > expression. However, an arbitrary expression can contain one or > more columns marked with the (+) operator. > > - A WHERE condition containing the (+) operator cannot be combined with > another condition using the OR logical operator. > > - A WHERE condition cannot use the IN comparison condition to compare a > column marked with the (+) operator with an expression. > > If the WHERE clause contains a condition that compares a column from > table B with a constant, then the (+) operator must be applied to the > column so that Oracle returns the rows from table A for which it has > generated nulls for this column. Otherwise Oracle returns only the > results of a simple join. > > In a query that performs outer joins of more than two pairs of tables, > a single table can be the null-generated table for only one other > table. For this reason, you cannot apply the (+) operator to columns > of B in the join condition for A and B and the join condition for B > and C. Refer to SELECT for the syntax for an outer join.

Taken from http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm

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

LEFT OUTER JOIN

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

RIGHT OUTER JOIN

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

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

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

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