Re: outer join syntax...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Tue, 20 Apr 2004 18:05:14 +0200
Message-ID: <c63hnq$qr4$1@nntp.fujitsu-siemens.com>

"Volker Hetzer" <volker.hetzer_at_ieee.org> schrieb im Newsbeitrag news:c63eej$a70$1_at_nntp.fujitsu-siemens.com...
> Hi!
> I'm comparing two tables in order to find out whether the column names differ.
> But, somehow I can't get oracle to join all_tab_columns the proper way.
> I'm using 9.2.0.4 on Linux.
> --Where is the result row with column2?
> select
> t1.table_name,t1.column_name, t2.table_name,t2.column_name
> from
> all_tab_columns t1 FULL JOIN all_tab_columns t2 on (t1.column_name = t2.column_name)
> where t1.owner = 'SPIELPLATZ'
> and t2.owner = 'SPIELPLATZ'
> and t1.table_name = 'TABLE1'
> and t2.table_name = 'TABLE2'
> order by t1.table_name;

Ok, seems I've got a query that gives me the expected results: select

                t1.column_name,t2.column_name
        from    
                all_tab_columns t1 FULL OUTER JOIN all_tab_columns t2
                        on
                                (
                                                t1.column_name = t2.column_name
                                        and t1.owner = 'SPIELPLATZ'
                                        and t2.owner = 'SPIELPLATZ'
                                        and t1.table_name = 'TABLE1'
                                        and t2.table_name = 'TABLE2'
                                )
        where
                        (
                                t1.owner = 'SPIELPLATZ'
                        and t1.table_name = 'TABLE1'
                        and t2.owner is null
                        )
                 or     (
                                t2.owner = 'SPIELPLATZ'
                        and t2.table_name='TABLE2'
                        and t1.owner is null
                        )
        order by t1.table_name;

And I think I even understend what the problem was. All examples I know of typically join only one column but what I forgot was that, when oracle can't find a matching row *all columns* of that side of the result set are empty.

However,
> --Why does t2 not have a column_name column?
> select
> t1.table_name,t1.column_name, t2.table_name,t2.column_name
> from
> all_tab_columns t1 FULL JOIN all_tab_columns t2 using (column_name)
> where t1.owner = 'SPIELPLATZ'
> and t2.owner = 'SPIELPLATZ'
> and t1.table_name = 'TABLE1'
> and t2.table_name = 'TABLE2'
> order by t1.table_name;

still remains.

Lots of Greetings!
Volker Received on Tue Apr 20 2004 - 11:05:14 CDT