Inner and Outer Join Examples

December 7, 2009

Some time ago someone asked the following in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/41863b746cbef8d2

Can someone please help me in understanding inner and outer joins?  I seem to have a block when it comes to understanding this concept.

 Short answer:
* Inner join – the value must be in BOTH tables
* Outer join – the value must be in at least ONE table

Assume that the following table and columns in those tables, and data in those columns exist:

TABLE_1.ANIMAL 
  COW 
  PIG 
  ZEBRA 
  SHARK 
  ROOSTER 
  LION

 

TABLE_2.ANIMAL 
  COW 
  PIG 
  DOG

 

TABLE_3.ANIMAL 
  ZEBRA 
  LION 
  TIGER

Inner join TABLE_2 and TABLE_3

SELECT 
  T2.ANIMAL, 
  T3.ANIMAL 
FROM 
  TABLE_2 T2, 
  TABLE_3 T3 
WHERE 
  T2.ANIMAL=T3.ANIMAL; 
 
(no results) 
-------------------  

(Left) Outer join TABLE_2 and TABLE_3, include all rows from TABLE_2

SELECT 
  T2.ANIMAL, 
  T3.ANIMAL 
FROM 
  TABLE_2 T2, 
  TABLE_3 T3 
WHERE 
  T2.ANIMAL=T3.ANIMAL(+); 
 
T2.ANIMAL      T3.ANIMAL 
COW            (null) 
PIG            (null) 
DOG            (null) 
-------------------

Inner join TABLE_1 and TABLE_2

SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL=T2.ANIMAL; 
 
T1.ANIMAL         T2.ANIMAL 
COW               COW 
PIG               PIG 
-------------------

(Right) Outer join TABLE_1 and TABLE_2, include all rows from TABLE_2

SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL(+)=T2.ANIMAL; 
 
T1.ANIMAL         T2.ANIMAL 
COW               COW 
PIG               PIG 
(null)            DOG 
-------------------

(One Method, Full) Outer join TABLE_1 and TABLE_2, include all rows

SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL(+)=T2.ANIMAL 
UNION 
SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL=T2.ANIMAL(+); 
 
T1.ANIMAL         T2.ANIMAL 
COW               COW 
PIG               PIG 
(null)            DOG 
ZEBRA             (null) 
SHARK             (null) 
ROOSTER           (null) 
LION              (null)