Home » SQL & PL/SQL » SQL & PL/SQL » outer join 2
outer join 2 [message #206987] Sun, 03 December 2006 01:02 Go to next message
BigLearner
Messages: 10
Registered: December 2006
Junior Member
Hai everyone,

This is what I get from one of the sites when I searched for outer joins but my book says having + on right means outer join and if its on left means its left join but the example is the other way round.

does it make a difference when we swap the join condition? or the order the tables are listed in the from clause?


SQL> select p.part_id, s.supplier_name
2 from part p right outer join supplier s
3 on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
Supplier#3



SQL> select p.part_id, s.supplier_name
2 from part p, supplier s
3 where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
Supplier#3

SQL> select p.part_id, s.supplier_name
2 from part p, supplier s
3 where p.supplier_id = s.supplier_id (+);

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P3
P4


SQL> select p.part_id, s.supplier_name
2 from part p left outer join supplier s
3 on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P4
P3

BigLearner
Re: outer join 2 [message #207031 is a reply to message #206987] Sun, 03 December 2006 23:46 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Some Notes about left and right outer joins

Left Outer Join

A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table.

Example
SQL > select empno,ename,sal,dname,loc
from dept d left outer join emp e
on e.deptno = d.deptno



Right Outer Join

A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.


Example

SQL> select empno,ename,sal,dname,loc
from emp e right outer join dept d
on e.deptno = d.deptno


SQL>select p.part_id, s.supplier_name
2 from part p, supplier s
3 where p.supplier_id (+) = s.supplier_id;

(+) sign is on the parts table side so it returns all the rows of suppliers table for which no corresponding parts.

SQL> select p.part_id, s.supplier_name
2 from part p, supplier s
3 where p.supplier_id = s.supplier_id (+);

(+) sign is on the suppliers table side so it returns all the rows of parts table for which no corresponding suppliers.
Re: outer join 2 [message #207081 is a reply to message #207031] Mon, 04 December 2006 01:56 Go to previous messageGo to next message
BigLearner
Messages: 10
Registered: December 2006
Junior Member
Hai Ramesh,

Thanks for the reply but this is my problem.

I am refering to my Oracle Exam Guide from ORacle Press which says left outer join would have the (+) sign on left side and right outer join would have it on right side.

The book infact gives a table with clear explanation that left outer join will have (=) on left side of the = and right outer join will have (+) it on the right side of the =

but all the explanations and examples i noticed on the web are just the other way round.

please advice me on whats right.

BigLearner

Re: outer join 2 [message #207147 is a reply to message #207081] Mon, 04 December 2006 05:27 Go to previous message
CoolBuddy
Messages: 10
Registered: December 2006
Location: India
Junior Member
Lets takle it this way. What Ramesh said is quite correct.
Just u need to understand that,

select empno,ename,sal,dname,loc
from dept d left outer join emp e
on e.deptno = d.deptno

is equivalent to

select empno,ename,sal,dname,loc
from dept d, emp e
where e.deptno(+) = d.deptno

and

select empno,ename,sal,dname,loc
from dept d right outer join emp e
on e.deptno = d.deptno

is equivalent with

select empno,ename,sal,dname,loc
from dept d, emp e
where e.deptno = d.deptno(+)


Its not the matter of the (+) sign be on left or right of (=) sign. It matters with which table is on left and right side, to which table u are going to join. Try to execute the query with some combinations and u will get the point, i suppose.

See if u can make up ur problem from it.
Previous Topic: CASE statement
Next Topic: Interchange of Columns
Goto Forum:
  


Current Time: Thu Dec 08 02:12:54 CST 2016

Total time taken to generate the page: 0.12195 seconds