How do you avoid column name conflicts?

Question

I was recently assigned a task of creating an auction system. During my work, I met numerous occasions where my SQL queries that contained joins failed to execute due to ambiguous column names. Consider this (simplified) table structure for the auction:

table auction:

• id
• name
• uid (ID of the user that created the auction)

table item:

• id
• name
• uid (ID of the user that added the item)
• aid (ID of the auction where the item is available)
• price (initial price)

table user:

• id
• name

table bid:

• id
• uid (ID of the user that placed a bid)
• iid (item whose price has been raised)
• price (offered price)

As you can see, there are numerous columns that have conflicting names. Joining these tables requires using some measures that will clear the ambiguities.

I can think of two ways to do this. First is to rename the columns, prefixing all of them with an abbreviated table name, so that auction ID will become a_id, item ID will become i_id, and item ID within the bid table will become b_i_id. This is pretty solid, but reduces the readability of the column names.

Another way I can think of is writing explicit queries:

SELECT `bid`.`id`, `user`.`name`, `bid`.`price`
FROM `bid`
JOIN `item` ON `item`.`id` = `bid`.`iid`
JOIN `user` ON `user`.`id` = `bid`.`uid`
JOIN `auction` ON `auction`.`id` = `item`.`aid`
WHERE `bid`.`price` > `item`.`price`
AND `auction`.`id` = 1
GROUP BY `user`.`id`
ORDER BY `bid`.`price` DESC;

This is readable and unambiguous, but requires lots of extra keystrokes.

I use the second approach, but maybe there are others that you have successfuly used in similar situations? How do you avoid column name conflicts in your SQL queries?

Answer 1

May be you can try using aliases on the table names ?

select * from table1 as T1
join table2 as t2 on (t1.key=t2.foreignkey)

Answer 2

You need to use AS alias.

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.

An alias name could be anything, but usually it is short.