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).