Outer Join & View

From: (wrong string) öth <dnoeth_at_gmx.de>
Date: Fri, 14 Jun 2002 22:09:56 +0200
Message-ID: <aedic8$6b343$1_at_ID-28204.news.dfncis.de>

Hi all,
can someone shed some light on following problem, i.e. what's the expected answer set according to ANSI SQL:

create table T_1
( Id_1 integer not null primary key, Desc_1 VARCHAR(6));

insert into T_1 values(0,'Text_0');
insert into T_1 values(1,'Text_1');
insert into T_1 values(2,'Text_2');
insert into T_1 values(3,'Text_3');

create table T_2

( Id_2 integer not null primary key, Desc_2 VARCHAR(6));

insert into T_2 values(0,'Text_0');
insert into T_2 values(2,'Text_2');
insert into T_2 values(3,NULL);

create view V_2 as
select
  coalesce(Id_2,0) as V_Id_2,
  coalesce(Desc_2, 'Error') as V_Desc_2
from T_2;

select T_1.Id_1, T_1.Desc_1, V_2.V_Id_2, V_2.V_Desc_2 from T_1 left join v_2 on T_1.Id_1 = V_2.V_Id_2 order by T_1.Id_1;

Answer set 1:

 Id_1  Desc_1    V_Id_2  V_Desc_2
    0  Text_0         0  Text_0
    1  Text_1         0  Error
    2  Text_2         2  Text_2
    3  Text_3         3  Error

Answer set 2:

 Id_1  Desc_1    V_Id_2  V_Desc_2
    0  Text_0         0  Text_0
    1  Text_1      NULL  NULL
    2  Text_2         2  Text_2
    3  Text_3         3  Error

Both answer sets are returned by different RDBMSs, but only _one_ may be the ANSI compliant result ;-(

Dieter Received on Fri Jun 14 2002 - 22:09:56 CEST