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
LEFT OUTER JOIN
SELECT * FROM A, B WHERE A.column = B.column(+)
RIGHT OUTER JOIN
SELECT * FROM A, B WHERE A.column (+)= B.column
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...
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
Source: Stackoverflow.com