Re: Oracle8i Left Join Question

From: Nicholas Carey <ncarey_at_speakeasy.org>
Date: Wed, 21 Nov 2001 02:13:46 -0000
Message-ID: <Xns915FB970E310Fncareyspeakeasyorg@207.126.101.92>

On 20 Nov 2001, Neil Zanella <nzanella_at_garfield.cs.mun.ca> spake and said:

> I know that Oracle 8i uses the notation = (+) for left Joins
> and that Oracle 9i uses the SQL LEFT OUTER JOIN syntax for
> this. Now I want to be able to do the left Join without
> using any of these two operators so as to obtain database
> independence in my implementation.
>
> Thinking along these lines I notice that postgresql allows
> me to do the following but Oracle8 complains about NULL
> being an incompatible datatype with the other column above:
>
> (SELECT A, B FROM T) UNION (SELECT A, NULL FROM T)
>
> The above is a simplification of what needs to be done to
> obtain the left outer join effect without using the = (+) or
> ANSI syntax.
>
> Here is what I get: ORA-01790: expression must have same
> datatype as corresponding expression
>
> Is there a way to circumvent this error in Oracle (other
> than using the join syntax)???

Your left outer join:

  select col_1, col_2, col_3
  from foo
  left join bar on foo.id = bar.id

is exactly equivalent to

    select *
    from foo
    join bar on foo.id = bar.id
  UNION
    select *
    from foo
    where not exists ( select * from bar

                       where bar.id = foo.id
                     )

What you're having problems with is that, in a UNION, the column list of each SELECT clause must have

1. the same number of columns. Column names don't matter -- they are picked up from the first SELECT clause.
2. Each column must have the same datatype.

You are having problems with item 2. Your union:

    select foo.id foo_id ,

           bar.id bar_id
    from foo
    join bar on bar.id = foo.id
  UNION
    select foo.id foo_id ,

           NULL bar_id
    from foo
    where not exists ( select * from bar

                       where bar.id = foo.id
                     )

is failing because the second SELECT clause in the UNION doesn't return the columns with exactly the same datatype [what is the data type of NULL? SQL doesn't know either. So it's different than the data type of the same column in the first SELECT clause.] Therefore, to make this query valid, you must explicitly cast the offending column to an appropriate datatype:

    select foo.id foo_id ,

           bar.id bar_id
    from foo
    join bar on bar.id = foo.id
  UNION

    select foo.id          foo_id ,
           to_number(NULL) bar_id

    from foo
    where not exists ( select * from bar

                       where bar.id = foo.id
                     )

Hope this helps! Received on Tue Nov 20 2001 - 20:13:46 CST