title: How do you avoid column name conflicts?
body: <p>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:</p>
  
  <p>table <code>auction</code>:</p>
  
  <ul>
  <li><code>id</code></li>
  <li><code>name</code></li>
  <li><code>uid</code> (ID of the user that created the auction)</li>
  </ul>
  
  <p>table <code>item</code>:</p>
  
  <ul>
  <li><code>id</code></li>
  <li><code>name</code></li>
  <li><code>uid</code> (ID of the user that added the item)</li>
  <li><code>aid</code> (ID of the auction where the item is available)</li>
  <li><code>price</code> (initial price)</li>
  </ul>
  
  <p>table <code>user</code>:</p>
  
  <ul>
  <li><code>id</code></li>
  <li><code>name</code></li>
  </ul>
  
  <p>table <code>bid</code>:</p>
  
  <ul>
  <li><code>id</code></li>
  <li><code>uid</code> (ID of the user that placed a bid)</li>
  <li><code>iid</code> (item whose price has been raised)</li>
  <li><code>price</code> (offered price)</li>
  </ul>
  
  <p>As you can see, there are numerous columns that have conflicting names. Joining these tables requires using some measures that will clear the ambiguities.</p>
  
  <p>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 <code>a_id</code>, item ID will become <code>i_id</code>, and item ID within the <code>bid</code> table will become <code>b_i_id</code>. This is pretty solid, but reduces the readability of the column names.</p>
  
  <p>Another way I can think of is writing explicit queries:</p>
  
  <pre><code>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` &gt; `item`.`price`
  AND `auction`.`id` = 1
  GROUP BY `user`.`id`
  ORDER BY `bid`.`price` DESC;
  </code></pre>
  
  <p>This is readable and unambiguous, but requires lots of extra keystrokes.</p>
  
  <p>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?</p>