SQL Server 中的 T-SQL - 错误还是功能? [英] T-SQL in SQL Server - bug or feature?

问题描述

我有以下选择:

SELECT DISTINCT pl 
FROM [dbo].[VendorPriceList] h
WHERE PartNumber IN (SELECT DISTINCT PartNumber 
                     FROM [dbo].InvoiceData 
                     WHERE amount > 10 
                       AND invoiceDate > DATEADD(yyyy, -1, CURRENT_TIMESTAMP)

                     UNION

                     SELECT DISTINCT PartNumber 
                     FROM [dbo].VendorDeals)

这里的问题是表 [dbo].VendorDeals 没有列 PartNumber，但是没有检测到错误并且查询适用于联合的第一部分.

The issue here is that the table [dbo].VendorDeals has NO column PartNumber, however no error is detected and the query works with the first part of the union.

更重要的是，IntelliSense 还允许和识别 PartNumber.这仅在复杂语句中失败.

Even more, IntelliSense also allows and recognize PartNumber. This fails only when inside a complex statement.

很明显，如果你限定列名，错误就会很明显.

It is pretty obvious that if you qualify column names, the mistake will be evident.

推荐答案

这不是 SQL Server/T-SQL 方言解析中的错误，不，这完全正常em> 正如预期的那样.问题或错误在于您的 T-SQL；特别是因为您没有限定您的列.由于我没有您的表的定义，我将首先提供示例 DDL:

This isn't a bug in SQL Server/the T-SQL dialect parsing, no, this is working exactly as intended. The problem, or bug, is in your T-SQL; specifically because you haven't qualified your columns. As I don't have the definition of your table, I'm going to provide sample DDL first:

CREATE TABLE dbo.Table1 (MyColumn varchar(10), OtherColumn int);
CREATE TABLE dbo.Table2 (YourColumn varchar(10) OtherColumn int);

然后是一个类似于您的查询的示例:

And then an example that is similar to your query:

SELECT MyColumn
FROM dbo.Table1
WHERE MyColumn IN (SELECT MyColumn FROM dbo.Table2);

这首先会解析；这是一个有效的查询.其次，假设 dbo.Table2 包含至少一行，那么 dbo.Table1 表中的每一行在 MyColumn 具有非 NULL 值时返回.为什么?好吧，让我们用表名来限定列，因为 SQL Server 会解析它们:

This, firstly, will parse; it is a valid query. Secondly, provided that dbo.Table2 contains at least one row, then every row from table dbo.Table1 will be returned where MyColumn has a non-NULL value. Why? Well, let's qualify the column with table's name as SQL Server would parse them:

SELECT Table1.MyColumn
FROM dbo.Table1
WHERE Table1.MyColumn IN (SELECT Table1.MyColumn FROM dbo.Table2);

请注意，IN 中的 inside 列也在引用 Table1，不是 Table2.默认情况下，如果一个列在子查询中省略了它的别名，它将被假定引用该子查询中定义的表.如果，然而，子查询中没有一个表有同名的列，那么它将被假定引用一个确实存在该列的表；在这种情况下 Table1.

Notice that the column inside the IN is also referencing Table1, not Table2. By default if a column has it's alias omitted in a subquery it will be assumed to be referencing the table(s) defined in that subquery. If, however, none of the tables in the sub query have a column by that name, then it will be assumed to reference a table where that column does exist; in this case Table1.

让我们举一个不同的例子，使用表格中的另一列:

Let's, instead, take a different example, using the other column in the tables:

SELECT OtherColumn
FROM dbo.Table1
WHERE OtherColumn IN (SELECT OtherColumn FROM dbo.Table2);

这将被解析如下:

SELECT Table1.OtherColumn
FROM dbo.Table1
WHERE Table1.OtherColumn IN (SELECT Table2.OtherColumn FROM dbo.Table2);

这是因为OtherColumn 存在于两个 表中.因为，在子查询中，OtherColumn 没有被限定，它假定所需的列是在同一范围内定义的表中的列，Table2.

This is because OtherColumn exists in both tables. As, in the subquery, OtherColumn isn't qualified it is assumed the column wanted is the one in the table defined in the same scope, Table2.

那么解决方案是什么?别名并限定您的列:

So what is the solution? Alias and qualify your columns:

SELECT T1.MyColumn
FROM dbo.Table1 T1
WHERE T1.MyColumn IN (SELECT T2.MyColumn FROM dbo.Table2);

不出所料，这会出错，因为 Table2 没有 MyColumn 列.

This will, unsurprisingly, error as Table2 has no column MyColumn.

就我个人而言，我建议除非您在查询中只引用了一个表，否则您可以对所有列进行别名和限定.这不仅可以确保不能引用错误的列(例如在子查询中)，而且还意味着其他读者确切地知道正在引用哪些列.它还可以防止将来出现故障.老实说，多年来我已经数不清有多少次由于模棱两可的列"而导致流程失败了.错误，由于表的定义被更改并且引用该表的查询未被开发人员正确限定...

Personally, I suggest that unless you have only one table being referenced in a query, you alias and qualify all your columns. This not only ensures that the wrong column can't be referenced (such as in a subquery) but also means that other readers know exactly what columns are being referenced. It also stops failures in the future. I have honestly lost count how many times over years I have had a process fall over due to the "ambiguous column" error, due to a table's definition being changed and a query referencing the table wasn't properly qualified by the developer...

这篇关于SQL Server 中的 T-SQL - 错误还是功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！