How to distinguish the same field names of two Oracle tables?

Question

I have two different table. One table has 70 Columes, the other has 80. I want to display all the Columes of the two tables. But there are some Columes with the same Columes-name.

EX:

SELECT * 
FROM TABLE1 A INNER JOIN
     TABLE2 B ON A.ID = B.ID

enter image description here

I want to distinguish which table does the Columes comes from.

I know have to list your column list explicitly and provide aliases to them in the SELECT list.

How can I modify my program?

Is there any other easier way.

Because there are too many field names

Answer 1

You'll have to list your column list explicitly and provide aliases to them in the SELECT list.

SELECT
    A.ID AS A_ID,
    B.ID AS B_ID
FROM TABLE1 A INNER JOIN
     TABLE2 B ON A.ID = B.ID

As a best practice

• Never use SELECT * in production queries, always list the required columns explicitly. Why is SELECT * considered harmful?
• When you have more than one table referenced in the query (e.g you join two tables), always give an alias to all tables and use that alias when you are referencing any columns of the tables.

Answer 2

You can build a query and let it to compose the query for you. It's not that hard as it seems

I've created tables TEST1 and TEST2 with identical column names and managed database to list me all the columns with prefixes.

select 'select ' txt from dual
union all
select listagg('t1.' || atc.COLUMN_NAME, ', ') within group (order by atc.COLUMN_NAME) || ', '
  from all_tab_cols atc
 where table_name = 'TEST1'
union all
select listagg('t2.' || atc.COLUMN_NAME, ', ') within group (order by atc.COLUMN_NAME)
  from all_tab_cols atc
 where table_name = 'TEST2';

The output is

TXT
----------
select
t1.NUM_COL, t1.TEXT_COL,
t2.NUM_COL, t2.TEXT_COL

So you may run the query, copy the output and then add the FROM and WHERE and other parts you need

Answer 3

If the only column names in common are used a JOIN keys, then you can phrase this as:

SELECT * 
FROM TABLE1 A INNER JOIN
     TABLE2 B 
     USING (ID);

The ID column only appears once in the result set.

If other columns are common, then you need to use column aliases. Sometimes, it is convenient to use something like this:

SELECT A.*, B.col1 as b_col1, B.col2 as b_col2, . . .
FROM TABLE1 A INNER JOIN
     TABLE2 B 
     USING (ID);

To make this simpler, you can use the metadata tables.