T-SQL“不在(选择"不工作(如预期) [英] T-SQL "not in (select " not working (as expected)

问题描述

我有一个普通的一对多关系:

I have an ordinary one-to-many relation:

customer.id = order.customerid

我想找到没有关联订单的客户.

I want to find customers who have no associated orders.

我试过了:

-- one record
select * from customers where id = 123

-- no records
select * from orders where customerid = 123

-- NO RECORDS
select * 
from customers 
where id not in (select customerid from orders)

-- many records, as expected.
select * 
from customers 
where not exist (select customerid from orders 
                 where customers.customerid = customer.id)

我错了，还是应该管用?

Am I mistaken, or should it work?

推荐答案

NOT IN 当 in-list 包含 NULL 值时，行为不符合预期.

NOT IN does not behave as expected when the in-list contains NULL values.

事实上，如果任何值是NULL，则根本不会返回任何行.请记住:在 SQL 中，NULL 表示不确定"值，而不是缺失值".因此，如果列表包含任何 NULL 值，那么它可能等于一个比较值.

In fact, if any values are NULL, then no rows are returned at all. Remember: In SQL, NULL means "indeterminate" value, not "missing value". So, if the list contains any NULL value then it might be equal to a comparison value.

因此，orders 表中的 customerid 必须为 NULL.

So, customerid must be NULL in the orders table.

出于这个原因，我强烈建议您始终将 NOT EXISTS 与子查询一起使用，而不是 NOT IN.

For this reason, I strongly recommend that you always use NOT EXISTS with a subquery rather than NOT IN.

这篇关于T-SQL“不在(选择"不工作(如预期)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！