How do you avoid column name conflicts?

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?

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)

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.

Your approach is correct, but you can also provide an alias for your table:

SELECT a.* FROM TableA A

in here you can refer to TableA as simply A.

The golden rule of thumb is that if you are ever using more than one table at all, alias the tables, and explicitly alias all columns.

This consistency will get you deep into the force, young padawan.

Change your naming convention so that each data element has a unique name in the schema e.g. auction_id, bid_id, user_id, etc. Ideally the name of the data element will not change between tables but sometimes you will need to add a qualifier to create a synonym e.g. adding_user_id and bidding_user_id if user_id appeared twice in the same table. You should document data element names and their synonyms in a data dictionary.

My experience is that that the benefits of the extra keystrokes outweighs the short time it takes to type them by far in the long run, at latest when you need to look at a query that you have written a year ago or so.

You can also define the table column beside the table name:

SELECT P.*,T.name AS type FROM <TABLE-A> AS P LEFT JOIN <TABLE-B> AS T ON P.id=T.id

I've faced with MySQLSyntaxErrorException:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank

after executing the following query:

insert into my_table (rank) values (1);

Because rank column name clashes with sql keyword rank.

SOLUTION:

I've added back quotes to rank column name and problem was solved:

insert into my_table (`rank`) values (1);

Another way to solve the problem is to use table name instead of back quotes:

insert into my_table (my_table.rank) values (1);