MS Access sql using NOT IN

Question

I am working in MS Access 2013 with these two tables.

t1:       t2:
ID        ID   F1   F2
1              a    a
2         1    b    a
3         2    b    a

I know it is not the most ideal way to do this, but I wrote this query to pull all IDs that dont exist in another table.

Select [ID] from table1 WHERE [ID] NOT IN (SELECT ID FROM table2)

The query should return 3, but for some reason this table gives me no results. When I change the query to this, it gives me 3 as the result.

Select [ID] from table1 WHERE [ID] NOT IN (SELECT [ID] FROM table2 WHERE [ID] IS NOT NULL)

I am unsure to why the logic of why the second query works, but the first one returns no results. When I run the nested query (SELECT [ID] FROM table2) it does not return 3 for either query, so both queries should work. Can anyone explain why the first query does not return anything?

Gordon Linoff · Accepted Answer · 2018-02-07T02:52:29.457

3

I strongly recommend that you simply forget using not in with subqueries. Instead, use not exists:

Select [ID]
from table1 as t1
where NOT EXISTS (SELECT 1 FROM table2 as t2 WHERE t2.id = t1.id);

As you have learned, not in has the wrong semantics (i.e. behavior) when the subquery returns NULL. Instead of trying to make it work, just use NOT EXISTS. An added bonus is that this readily extends to multiple columns and should use an index if available.

edited Feb 07 '18 at 02:52

answered Feb 06 '18 at 18:24

Gordon Linoff

1,242,037
58
646
786

the NOT EXISTS does not seem to work. it does not return anything when I switch to NOT EXISTS – J. Doe Feb 06 '18 at 19:27
@J.Doe Also remember for `Exists` the important part is `t2.id = t1.id`. It doesn't matter what the `Select` says. Often times people will write `Select *` because it isn't even evaluated. – Brad Feb 06 '18 at 21:43
1

@Brad . . . In fact, I usually use `select 1` because it is easy to type. – Gordon Linoff Feb 07 '18 at 02:52
ahh was missing the t2.id = t1.id part. so this makes the second SELECT query return a single id if it exists, as opposed to a whole list of existing IDs. how come NOT EXISTS does not work with the whole list? – J. Doe Feb 07 '18 at 20:04
@J.Doe . . . Because the uncorrelated query returns at least one row. – Gordon Linoff Feb 08 '18 at 04:14
oh right cause the WHERE is no longer using ID. Thank you! – J. Doe Feb 08 '18 at 15:59

score 1 · Answer 2 · answered Feb 06 '18 at 17:57

1

Null values cannot be evaluated therefore when the query is trying to see if 3 is = to null it doesn't know how to check that so it doesn't return anything.

answered Feb 06 '18 at 17:57

isaace

3,336
1
9
22

score 1 · Answer 3 · answered Feb 06 '18 at 18:00

1

This is because of how In works (and Not In, of course) spelled out in detail in the answer to this question: SQL "select where not in subquery" returns no results

answered Feb 06 '18 at 18:00

elc

1,962
20
24

score 0 · Answer 4 · answered Feb 06 '18 at 19:59

If you insist on using NOT IN, use this (assuming ID can't be negative in table2):

Select ID
from table1 
WHERE ID
NOT IN 
(
SELECT Nz(ID,-1) 
FROM table2
)

Nz converts a Null value to the provided parameter. If no parameter is provided, Nz converts to zero or a zero-length string.

MS Access sql using NOT IN

4 Answers4