Some understanding about joins

Showing 1-6 of 6 messages
Some understanding about joins tawright915 9/21/06 1:52 PM
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

Re: Some understanding about joins Charles Hooper 9/21/06 6:57 PM

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;

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.

Re: Some understanding about joins tawright915 9/22/06 6:38 AM
Thank you so much. The light went on.

Re: Some understanding about joins john....@newscale.com 9/26/06 9:31 PM
There's a good discussion at http://en.wikipedia.org/wiki/Join_(SQL)

Re: Some understanding about joins Channesh 9/27/06 7:58 PM
Charles Hooper,
 
 Beautifully put answer!

regards,
Channesh

Re: Some understanding about joins siva 9/29/06 9:18 PM
Thanks Charles Hooper,

its really a nice reply it helped me to get out of the confusion
between the both

thanking you once again

regards

siva.