Re: Outer join with constants

In article <84555688.0409170529.11509dc3_at_posting.google.com>,  laojiqi_at_hotmail.com (LJQ) wrote:

> Hello, Gurus,
>
> Since Tom is on vacation, I would like to hear from you.
> For ANSI outer join syntax, how oracle handle the following and what
> result can we expect?
>
> I KNOW there is no real-world meaning, I just want to know how oracle
> takes these.
>
> SELECT * FROM J1 left outer join J2 on (j1.c1 = j2.c1 and 1=1);
> SELECT * FROM J1 left outer join J2 on (j1.c1 = j2.c1 and 1=2);
>
> -----------------------------------------------------------------
>
> SELECT * FROM J1 full outer join J2 on (j1.c1 = j2.c1 and 1=1);
> SELECT * FROM J1 full outer join J2 on (j1.c1 = j2.c1 and 1=2);
>

Come on now. What does running it on Oracle have to do with it? These are all well defined selects. The two with 1=1 are equivalent to the same selects without the "and 1=1". The left join with 1=2 returns all rows of J1 with nulls in the J2 columns. The full join with 1=2 does that, and additionally outputs all rows of J2 with nulls in the J1 columns.

If Oracle does anything different, it's a bug.

For your homework, you can figure out what writing "or 1=1" instead of "and 1=1" does.

Karl Received on Fri Sep 17 2004 - 12:10:59 CDT