sql-server - TSQL "<> ANY(subquery)"

Question

我有一个关于 Any-Operator 的问题。

在 Technet 上它说

For example, the following query finds customers located in a territory not covered by any sales persons.

Use AdventureWorks2008R2;
GO
SELECT
    CustomerID
FROM
    Sales.Customer
WHERE
    TerritoryID <> ANY
    (
        SELECT
            TerritoryID
        FROM
            Sales.SalesPerson
    );

进一步

The results include all customers, except those whose sales territories are NULL, because every territory that is assigned to a customer is covered by a sales person. The inner query finds all the sales territories covered by sales persons, and then, for each territory, the outer query finds the customers who are not in one.

但是该查询返回所有客户。我将客户 TerritoryID 更新为 sales.person 没有的值，但该查询仍然返回所有客户，而不是我期望的客户..

我错过了什么吗？也许 Technet 上的那篇文章根本就是错误的吗？ https://technet.microsoft.com/de-de/library/ms187074(v=sql.105).aspx (德语)

有一名客户的 TerritoryID = 13

内部查询结果(SELECT TerritoryID FROM Sales.SalesPerson):424365146116918107

表 Sales.Customer 中有一行 CustomerID = 13，这是销售人员未涵盖的行。

Answer 1

create table #t1
(
id int
)

insert into #t1
values(1),(2),(3)

如您所见，T1 有三个值

现在让我们看看 Any 是如何工作的

当“is Equal to”与any一起使用时，其作用类似于IN

select * from #t1 where id=
any(select 0)--no result

当 Any 与 > 或 <> 一起使用时，Any 表示获取所有大于最小值的值

select * from #t1 where id<>
any(select 1)--2,3

select * from #t1 where id<>
any(select 0)--1,2,3

如果子查询返回一个值，外部查询将尝试获取大于内部查询的值