子查询列名称中的错误大小写会导致不正确的结果，但没有错误

Question

使用SQL Server Management Studio，我得到了一些不希望的结果（对我来说看起来像一个错误..？

如果我使用（字段而不是other_table字段）：

SELECT * FROM main_table WHERE field IN (SELECT FIELD FROM other_table)

我从main_table得到所有结果。

使用正确的大小写：

SELECT * FROM main_table WHERE field IN (SELECT field FROM other_table)

我得到了预期的结果，其中字段出现在其他字段中。

自行运行子查询：

SELECT FIELD FROM other_table

我收到无效的列名错误。

当然，在第一种情况下我应该得到这个错误吗？

这与排序规则有关吗？数据库是二进制排序规则。但是，服务器不区分大小写。在我看来，服务器组件在说"这段代码没问题"，并且不允许数据库说该字段是错误的名称..？

我有哪些解决方案选择？

Answer 1

让我们使用不依赖于区分大小写的东西来说明发生了什么：

USE tempdb;
GO
CREATE TABLE dbo.main_table(column1 INT);
CREATE TABLE dbo.other_table(column2 INT);
INSERT dbo.main_table SELECT 1 UNION ALL SELECT 2;
INSERT dbo.other_table SELECT 1 UNION ALL SELECT 3;
SELECT column1 FROM dbo.main_table
WHERE column1 IN (SELECT column1 FROM dbo.other_table);

结果：

column1
-------
1
2

为什么这不会引发错误？SQL Server 正在查看您的查询，并发现里面的 column1 不可能在other_table中，因此它正在推断并"使用"存在于外部引用表中的 column1（就像您可以引用仅存在于外部表中而没有表引用的列一样）。考虑一下这种变化：

SELECT [column1] FROM dbo.main_table
WHERE EXISTS (SELECT [column1] FROM dbo.other_table WHERE [column2] = [column1]);

结果：

column1
-------
1

同样，SQL Server 知道 where 子句中的 column1 在本地引用的表中也不存在，但它会尝试在外部作用域中找到它。因此，在一个虚构的世界中，您可能会认为查询实际上是在说：

SELECT m.[column1] FROM dbo.main_table AS m
WHERE EXISTS (SELECT m.[column1] FROM dbo.other_table AS o WHERE o.[column2] = m.[column1]);

（这不是我键入它的方式，但是如果我以这种方式键入它，它仍然有效。

在某些情况下，这在逻辑上没有意义，但这是查询引擎执行此操作的方式，并且必须一致地应用规则。在你的情况下（没有双关语），你有一个额外的复杂性：区分大小写。SQL Server 在子查询中找不到FIELD，但它确实在外部查询中找到了它。所以有几个教训：

始终使用
1. 表名或别名作为列引用的前缀（并始终使用架构作为表引用的前缀）。
2. 始终使用一致的大小写创建和引用表、列和其他实体。尤其是在使用二进制或区分大小写的排序规则时。

Answer 2

非常有趣的发现。 不言而喻的任务是，您始终应该在子查询中为表添加别名，并使用这些别名来明确您的列来自哪个表。 子查询允许您引用外部查询中的字段，这是导致问题的原因，但在您的场景中，我同意默认值应该是内部查询的字段列表，或者给您列歧义错误。 无论如何，以下方法始终是可取的：

select * from main_table a where a.field in (select x.field from other_table x)