Re: Outer join with constants

On 18 Sep 2004 00:28:41 -0700, laojiqi_at_hotmail.com (LJQ) wrote:

>I am going to give you more facts to wake you up:
>
>SQL> select * from j1;
>
>C1 C2
>---- ----------
>a 1
>b 2
>
>SQL> select * from j2;
>
>C1 C2
>---- ----------
>b 2
>c 3
>
>select * from j1 left outer join j2 on (j1.c1 = j2.c1 and 1=1)
>
>C1 C2 C1 C2
>---- ---------- ---- ----------
>b 2 b 2
>a 1
>
>select * from j1 left outer join j2 on (j1.c1 = j2.c1 and 1=0)
>
>C1 C2 C1 C2
>---- ---------- ---- ----------
>b 2 b 2
>a 1
>
>SQL> select * from j1 full outer join j2 on (j1.c1 = j2.c1 and 1= 0);
>
>C1 C2 C1 C2
>---- ---------- ---- ----------
>b 2 b 2
>a 1
>
>select * from j1 full outer join j2 on (j1.c1 = j2.c1 and 1= 1)
>C1 C2 C1 C2
>---- ---------- ---- ----------
>b 2 b 2
>a 1
> c 3
>
>Please don't be so confident when you really do not exactly know how
>oracle implement these. Do you home work first before your conclusion.
>Although I donot want to be offensive.

 But as Karl says, "If Oracle does anything different, it's a bug." - and it certainly appears it is.

 You didn't say what version of Oracle you got the results above on, but 9.2.0.5 gives different results for the 1=0 cases - the correct results. If you got the results you posted, you should patch your Oracle.

[oracle_at_testbox tmp]$ sqlplus test/test @ljq

SQL*Plus: Release 9.2.0.5.0 - Production on Sat Sep 18 15:03:55 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> drop table j1;

Table dropped.

SQL> drop table j2;

Table dropped.

SQL> create table j1 (c1 varchar2(1), c2 number);

Table created.

SQL> create table j2 (c1 varchar2(1), c2 number);

Table created.

SQL> insert into j1 values ('a', 1);

1 row created.

SQL> insert into j1 values ('b', 2);

1 row created.

SQL> insert into j2 values ('b', 2);

1 row created.

SQL> insert into j2 values ('c', 3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from j1 left outer join j2 on (j1.c1 = j2.c1 and 1=1);

C C2 C C2
- ---------- - ----------

a          1
b          2 b          2

SQL> select * from j1 left outer join j2 on (j1.c1 = j2.c1 and 1=0);

C C2 C C2
- ---------- - ----------

a          1
b          2

SQL> select * from j1 full outer join j2 on (j1.c1 = j2.c1 and 1=0);

C C2 C C2
- ---------- - ----------

a          1
b          2
             b          2
             c          3

SQL> select * from j1 full outer join j2 on (j1.c1 = j2.c1 and 1=1);

C C2 C C2
- ---------- - ----------

a          1
b          2 b          2
             c          3

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool