Ambiguous column name - right?

Question

If I want to write a query with a simple connection, I can do this:

select * from customer c join order o on c.customerid = o.customerid where c.customerid = 100 

and everything works fine. There is a reason in this query for which I should specify a table alias - i.e. c.customerid ? Why can't I just write this:

 select * from customer c join order o on c.customerid = o.customerid where customerid = 100 

I get an Ambiguous column name 'customerid' error message. In this case, where is there only one column in the WHERE clause, and this is the column I am connecting to, is it really "ambiguous"? Or is it just conforming to the ansi standard (I assume here - I don't know if it conforms) and promote good coding conventions?

Answer 1

In your specific example, I cannot think of any circumstances in which this could make a difference. However, for an INNER JOIN in a row column, it can do as shown below.

 DECLARE @customer TABLE (customerid CHAR(3) COLLATE Latin1_General_CI_AS) INSERT INTO @customer VALUES('FOO'); DECLARE @order TABLE (customerid CHAR(3) COLLATE Latin1_General_CS_AS) INSERT INTO @order VALUES('FOO'); SELECT * FROM @customer c JOIN @order o ON c.customerid = o.customerid COLLATE Latin1_General_CS_AS WHERE c.customerid = 'Foo' /*Returns 1 row*/ SELECT * FROM @customer c JOIN @order o ON c.customerid = o.customerid COLLATE Latin1_General_CS_AS WHERE o.customerid = 'Foo' /*Returns 0 rows*/ 

Answer 2

Omitting the table alias does make an ambiguous reference to the column. Just do join a left join and you will immediately see why:

 select * from customer c left join order o on c.customerid = o.customerid where customerid = 100 -- here, the semantics are quite different 

Another reason: one column may be of type INTEGER , the other type is SMALLINT . Which one to use for the filter? (This may have implications for the implementation plan). Martin Smith gives an even better example.

So, in general, you would not win by making SQL more "forgiving" while introducing new sources of errors. What you can do with some databases (and not with SQL Server), however this is:

 select * from customer c join order o using (customerid) where customerid = 100 

Or this (if clientid is the only common column name)

 select * from customer c natural join order o where customerid = 100 

Answer 3

You get an error because the customerid column exists in both order tables and client tables, and SQL does not know in which column the condition should be applied.

Answer 4

After JOINing two tables, the resulting table contains 2 columns with the same customerid name. Therefore, you need to specify a WHERE that uses the column by adding the table name as a prefix.

Answer 5

Well ... you know that the result set will only contain records with exactly the same customerid , however the database server does not do this because it does not "understand" what you are specifying. And if you have a connection in which there are no identical client identifiers, then you will be happy that the server distinguishes them .;)

Answer 6

A minor column error occurs only when we need to perform some operation in a field that has more than one table, so in this case SQL cannot recognize which table it should work from.