Re: SQL statement question...

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 29 Oct 1999 17:05:02 GMT
Message-ID: <7vck3u$gq9$2@news.seed.net.tw>

Jimmy <c6635500_at_comp.polyu.edu.hk> wrote in message news:38190B96.67042BBD_at_comp.polyu.edu.hk...
> Hello all,
>
> Table AAA:
> A1 number(10);
> A2 varchar2(10);
> A3 date;
>
> A1 A2 A3
> ----- ----- ------
> 1 A1 01-Jan-90
> 2 A2 02-Jan-90
>
> Table BBB
> B1 number(10);
> A1 number(10);
> B2 varchar2(100);
>
> B1 A1 B2
> ----- ----- ------
> 1 1 B1
> 2 3 B2
>
> Results:
> A1 A2 A3 B1 B2
> ----- ------ ------------ ----- ----
> 1 A1 01-Jan-90 1 B1
> 2 A2 02-Jan-90
> 3 2 B2
>
> How can I get the results by using one SQL statement? (i.e. retrieve
> all the fields of table AAA and BBB, join by A1. Also show the record
> content if the record is found in one table only)
>
> Thanks.
> Jimmy

What you want to do is so-called a "Full Join". This is not directly supported by Oracle. You can use a "Union All" and two "Outer Join" to perform it:

select aaa.a1, aaa.a2, aaa.a3, bbb.b1, bbb.b2   from aaa, bbb
  where aaa.a1=bbb.a1(+)
union all
select bbb.a1, aaa.a2, aaa.a3, bbb.b1, bbb.b2   from aaa, bbb
  where aaa.a1(+)=bbb.a1
    and aaa.a1 is null; Received on Fri Oct 29 1999 - 12:05:02 CDT