I am getting an error with the following query that uses a right join. My table has a column named 'id'.

FROM   dbo.tbldata
       RIGHT JOIN tblimg
               ON tbldata.id = tblimg.newsid
ORDER  BY createdDate DESC 

The error is:

Ambiguous column name 'id'


The error is referring to "id" in the first line of your query. SQL Server doesn't know whether you are referring to tbldata.id or tblimg.id.

It is good practice to always use an alias so that table schema changes are less likely to break your query:

SELECT TOP 6 td.id
FROM   dbo.tbldata td
       RIGHT JOIN tblimg ti
               ON td.id = ti.newsid
ORDER  BY createdDate DESC 
  • 6
    Besides aliasing columns, another best practice is to name columns with a descriptor in addition to the class, such as CustomerID instead of just ID. – Dan Guzman Jan 17 '19 at 12:26
  • 2
    ^ what Dan said, x1000. You should be able to recognize a column identifier like ID as what it represents, no matter where you find it in the schema or in a query. Some would argue it's redundant in its own table but you're rarely trying to figure out what it is when you're just looking at the table in isolation. Consistency is more important than avoiding redundancy IMHO. – Aaron Bertrand Jan 17 '19 at 13:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.