带有INNER JOIN的列名不明确的SQL错误:为什么? [英] Ambiguous column name SQL error with INNER JOIN: Why?

问题描述

以下代码将用于从两个表中选择数据:

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

我写得很容易

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

t1.foo或t2.foo:一打或六打中的六个.为什么不只是foo?

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

我一直想知道为什么SQL Server不会自动返回数据而无需我指定一个表或另一个表，因为选择是完全任意的(据我所知).

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?

推荐答案

因为MS SQL中的相等并不意味着它们在所需值中相等.考虑以下两个foo值:"Bar"，"baR".由于比较中固有的不区分大小写，因此Sql会认为它们在连接方面是相等的，但是您要求的是哪一个呢? SQL Server不知道，也无法猜测.您必须明确地告诉它.

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.

随着@Lukas Eder的提出，并非所有SQL实现都对用例不敏感.我知道MS SQL使用不区分大小写的方式，因此我的回答就是考虑到这一概念.

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.

这篇关于带有INNER JOIN的列名不明确的SQL错误:为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！