tawright915 wrote:
> Can someone please help me in understanding inner and outer joins? I
> seem to have a block when it comes to understanding this concept.
> And please if you have nothing helpful to contribute then please do not
> reply.
> Thanks
> Tom
* 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;
TABLE_1.ANIMAL TABLE_2.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;
TABLE_1.ANIMAL TABLE_2.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(+);
TABLE_1.ANIMAL TABLE_2.ANIMAL
COW COW
PIG PIG
(null) DOG
ZEBRA (null)
SHARK (null)
ROOSTER (null)
LION (null)
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
