MS Access-WHERE IN有效,但WHERE NOT IN失败 [英] MS Access - WHERE IN works, but WHERE NOT IN fails
本文介绍了MS Access-WHERE IN有效,但WHERE NOT IN失败的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在MS Access上有以下查询(简体):
I have the following query (simplified) on MS Access:
SELECT * FROM table1 WHERE table1.ID NOT IN (SELECT DISTINCT table1id FROM table2);
我的问题是它不起作用,但是这两个起作用:
My problem is it doesn't work, but these two ones work:
SELECT * FROM table1 WHERE table1.ID IN (SELECT DISTINCT table1id FROM table2);
SELECT * FROM table1 WHERE table1.ID NOT IN (2, 3);
第一个简单地返回一个空集,而我知道我在table1
上有ID为1到9的记录,并且表2上仅使用了2和3.
The first one simply returns me an empty set, while I know I have records on table1
with ids ranging from 1 to 9, and only 2 and 3 are use on table 2.
有帮助吗?
推荐答案
通常,IN和NOT in的问题与子选择中的NULL有关.试试这个,看看是否有效:
Generally, the problem with IN and NOT in has to do with NULLs in the subselect. Try this and see if it works:
SELECT *
FROM table1
WHERE table1.ID NOT IN (SELECT DISTINCT table1id FROM table2 where tableid is not null);
这篇关于MS Access-WHERE IN有效,但WHERE NOT IN失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文