Identify column names by table names when using SELECT *
I am joining multiple tables in a select statement and I want to add the tablenames to the column names so I can trace its origin. How can I do this and still use the *?
I am joining multiple tables in a select statement and I want to add the tablenames to the column names so I can trace its origin. For example:
select * from A left join B on B.ID=A.ID
This will return all columns of A and B, such as if A has Name and Address as its fields, and B has Name.
Name Address Name data datadata data
However, I would like to have the output as:
A.Name A.Address B.Name data datadata data
How can I do this and still use the *?
You cannot.
You can give each column an alias name, like this:
select A.Name as A_Name
, A.Address as A_Address
, B.Name as B_Name
from A
left outer
join B
on B.ID = A.ID
A_Name A_Address B_Name
data datadata data
However, you cannot do it any other way. Besides, you should not be using the dreaded, evil "select star" anyway. See Why "select star" is bad (9 March 2004).
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments