Re: Outer join

From: Spencer <spencerp_at_swbell.net>
Date: Wed, 16 May 2001 21:41:10 -0500
Message-ID: <YQGM6.203$ge6.147308@nnrp2.sbc.net>

although some relational databases support this type of outer join (e.g. in Teradata, the clause is "FULL OUTER JOIN"), this is not supported in Oracle 8.0.6 or earlier, and AFAIK, not in 8.1.x either.

here is one workaround to get the same result set: combine two queries with the UNION ALL operator, the first query does a "left" outer join, the second query returns the "missing" rows that would have been returned by a "right" outer join.

(note: reading the following SQL is easier in a fixed font)

SELECT a1.CD_A
     , a1.DESC_A
     , b1.CD_B
     , b1.DESC_B
  FROM A a1
     , B b1

 WHERE a1.CD_A = b1.CD_B(+)
UNION ALL
SELECT TO_NUMBER(NULL)

     , TO_CHAR(NULL)
     , b2.CD_B
     , b2.DESC_B

  FROM B b2
 WHERE NOT EXISTS ( SELECT 1

                      FROM A a2
                     WHERE a2.CD_A = b2.CD_B )

HTH "Diabolik" <Diabolik_at_Ginko.fr> wrote in message news:9dtlm4$cv0$1_at_pinco.nettuno.it...
> Suppose i've 2 tables A and B
>
> A
> ---
> cd_A
> desc_a
>
> B
> --
> cd_b
> desc b
>
> I can do an uter join like this
>
> where A.cd_a = B.cd_b (+)
>
> or
>
> where A.cd_a (+) = B.cd_b
>
> Do exists a method to do the next:
>
> where A.cd_a (+) = B.cd_b (+)?
>
>
>
>
>
>
>
>
>
Received on Wed May 16 2001 - 21:41:10 CDT