## Inner and Outer Join Examples

8 12 2009

December 7, 2009

Some time ago someone asked the following in a forum:

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

* 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;

-------------------  ```

(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)```