Ambiguous column name SQL error with INNER JOIN: Why?

Question

The following code will work to select data from two tables:

SELECT t1.foo, t2.bar FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.foo=t2.foo

I could just as easily written

SELECT t2.foo, t2.bar FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.foo=t2.foo

t1.foo or t2.foo: six of one or half a dozen of the other. Why not just foo?

I've been wonder why doesn't the SQL server just automatically return the data without me specifying one table or the other since the choice is entirely arbitrary (as far as I can tell).

I can make up a scenario where you would need to specify the table, such as

SELECT t1.foo, t2.bar FROM TABLE1 t1 INNER JOIN TABLE t2 ON t1.foo+=t2.foo

However, such scenarios are far from the norm in my experience.

Can anyone enlighten me as to why the language is designed so that I have to make this seemingly arbitrary decision in my code?

Answer 1

Because equality in MS SQL doesn't necessarily mean they are equal in the values you want. Consider the following 2 values for foo: "Bar", "baR". Sql will believe them to be equal with respect to the join because of the case insensitivity inherent in the comparison, but which one were you asking for? SQL Server doesn't know, and it can't guess. You must explicitly tell it.

Edit:As @Lukas Eder brought up, not all implementations of SQL use case insensitive comparisons. I know MS SQL uses case insensitivity, and my answer is geared with this concept in mind.

Answer 2

Your reasoning is not quite true. While t1.foo = t2.foo may hold true, that doesn't mean they're the same. Some examples:

• One could be VARCHAR(1) the other VARCHAR(2)
• One could be VARCHAR(1) the other NUMBER(1)
• t1 could be a simple table, whereas t2 is a view (or nested select) that makes hyper-complex calculations for the value of foo. The projection cost of either foo might not be the same in some RDBMS.

And there are dozens of other reasons, why it would be ambigous to just write foo

Answer 3

If you're sure that the columns represent the same thing you could join with a USING clause.

SELECT foo, t2.bar FROM TABLE1 t1 INNER JOIN TABLE2 t2 USING (foo);

Otherwise there's no guarantee that t1.foo is the same thing as t2.foo