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 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
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading