outer join syntax...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Tue, 20 Apr 2004 17:09:06 +0200
Message-ID: <c63eej$a70$1@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.

Here's the testcase:
col TABLE_NAME format a15;
col COLUMN_NAME format a15;
drop table table1;
create table table1 (column1 number, column2 varchar(32), column3 number); drop table table2;
create table table2 (column1 number, column3 number);

--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;

--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;

exit;

Also, as you can see when executing the script, the second select fails because t2 doesn't seem to have a column column_name. Why?

Lots of Greetings and thanks!
Volker Received on Tue Apr 20 2004 - 10:09:06 CDT