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. 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 *?

    Requires Free Membership to View

    Login

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

This was first published in July 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top