问题描述
我有一个普通的一对多关系:
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“不在(选择"不工作(如预期)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！