Re: Outer Join

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 5 Dec 2006 04:56:55 -0800
Message-ID: <1165323414.954716.316170@j72g2000cwa.googlegroups.com>

BigLearner wrote:
> Thanks a bunch for your reply.
>
> That really makes me feel better.
>
> Does the order of the FROM or ON clause matter?
>
> I did try and got some output but I don't understand how the output
> works.
>
> Sorry if thats a lot to ask.
>
> Thanks a bunch once again.
>
> BigLearner

I suspect that I know the answer, but let's confirm. Use the sample tables from the other post:

CREATE TABLE TABLE_1 (ANIMAL VARCHAR2(15));
CREATE TABLE TABLE_2 (ANIMAL VARCHAR2(15));
CREATE TABLE TABLE_3 (ANIMAL VARCHAR2(15));

INSERT INTO TABLE_1 VALUES ('COW');
INSERT INTO TABLE_1 VALUES ('PIG');
INSERT INTO TABLE_1 VALUES ('ZEBRA');
INSERT INTO TABLE_1 VALUES ('SHARK');

INSERT INTO TABLE_1 VALUES ('ROOSTER');
INSERT INTO TABLE_1 VALUES ('LION');

INSERT INTO TABLE_2 VALUES ('COW');
INSERT INTO TABLE_2 VALUES ('PIG');
INSERT INTO TABLE_2 VALUES ('DOG');

INSERT INTO TABLE_3 VALUES ('ZEBRA');

INSERT INTO TABLE_3 VALUES ('LION');
INSERT INTO TABLE_3 VALUES ('TIGER'); COMMIT; SELECT
  T2.ANIMAL T2_ANIMAL,
  T3.ANIMAL T3_ANIMAL
FROM
  TABLE_2 T2,
  TABLE_3 T3
WHERE
  T2.ANIMAL=T3.ANIMAL(+); T2_ANIMAL T3_ANIMAL
=============== ===============
DOG
PIG
COW SELECT
  T2.ANIMAL T2_ANIMAL,
  T3.ANIMAL T3_ANIMAL
FROM
  TABLE_2 T2,
  TABLE_3 T3
WHERE
  T2.ANIMAL(+)=T3.ANIMAL; T2_ANIMAL T3_ANIMAL
=============== ===============

		TIGER
		LION
		ZEBRA

SELECT
  T2.ANIMAL T2_ANIMAL,
  T3.ANIMAL T3_ANIMAL
FROM
  TABLE_2 T2 LEFT OUTER JOIN TABLE_3 T3 ON T2.ANIMAL=T3.ANIMAL; T2_ANIMAL T3_ANIMAL
=============== ===============
DOG
PIG
COW SELECT
  T2.ANIMAL T2_ANIMAL,
  T3.ANIMAL T3_ANIMAL
FROM
  TABLE_2 T2 LEFT OUTER JOIN TABLE_3 T3 ON T3.ANIMAL=T2.ANIMAL; T2_ANIMAL T3_ANIMAL
=============== ===============
DOG
PIG
COW
(Same answer as the above)

SELECT
  T2.ANIMAL T2_ANIMAL,
  T3.ANIMAL T3_ANIMAL
FROM
  TABLE_3 T3 LEFT OUTER JOIN TABLE_2 T2 ON T3.ANIMAL=T2.ANIMAL; T2_ANIMAL T3_ANIMAL
=============== ===============

		TIGER
		LION
		ZEBRA

(essentially the same as the right outer join using the (+) syntax)

SELECT
  T2.ANIMAL T2_ANIMAL,
  T3.ANIMAL T3_ANIMAL
FROM
  TABLE_2 T2 RIGHT OUTER JOIN TABLE_3 T3 ON T3.ANIMAL=T2.ANIMAL; T2_ANIMAL T3_ANIMAL
=============== ===============

		TIGER
		LION
		ZEBRA

(returns the same result as the left outer join directly above and the right outer join using the (+) syntax)

Repeat the above using TABLE_1 and one of the other two tables.

Look over the results, and see if you can answer your question: * Does the order of the FROM or ON clause matter?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Dec 05 2006 - 06:56:55 CST