Required to create an empty table by joining multiple tables in Oracle DB

Question

I got an error while creating an empty table by joining two tables.

I have two tables tab1 & tab2 and there are many common columns names in both tables.

I tried this:

create table tab3 as
select * from tab1 a, tab2 b where a.id = b.id and 1=2;

This gave ORA-00957: duplicate column name. As I mentioned above there are many common columns name between these two tables. If I prepare a create table statement by writing around 500 column names one by one then it will consume lots of time. Please help me out here.

Answer 1

The simple answer is, don't use *. Or is that the whole point, to avoid writing five lines of column names?

One way to avoid these conflicts, but that assumes that you are joining on all columns with the same name in both tables and on no other columns, is to do something like

create table new_table as
    select * 
    from   table_a natural join table_b
    where  null is not null
;

(As you can guess, as an aside, I prefer null is not null to 1 = 2; the parser seems to prefer it too, as it will rewrite 1 = 2 as null is not null anyway.)

Will you need to control the order of the columns in the new table? If you do, you will need to write them out completely in the select clause, regardless of which join syntax you choose to use.

Answer 2

That's an interesting question.

The only idea I have to offer it to let another query to compose the query you need

select 'select ' || listagg(col_name, ', ') within group(order by 1) || 'from tab1 a,tab2 b where (a.id=b.id) and 1=2' 
  from (select 'a.' || column_name col_name from user_tab_cols where table_name = 'TAB1'
        union all 
        select 'b.' || column_name from user_tab_cols where table_name = 'TAB2')

Please be aware for subqueries you need to specify table names in the upper case