Change NOT IN query to NOT EXISTS

[John]

Hi

Is there a way to convert the following NOT IN query into an equivalent NOT
EXISTS query as NOT IN query sometimes does not seem to work.

Thanks

Regards


SELECT tbl1.*
From tbl1
WHERE tbl1.ID Not In (SELECT tbl2.ID FROM tbl2 WHERE ...)

 

[Jeff Boyce]

"... sometimes does not seem to work ..."

Doesn't give us much to diagnose from... You appear to have decided to try
some other syntax rather than figure out what "isn't working".

More specific descriptions may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sylvain Lafontaine]

The presence of Null is usually the cause of trouble for a not-working NOT
IN query. Add the test « And tbl2.Id is Not Null » to your subquery and
probably that it will be OK. If not, give some more details about why you
think your query sometimes does not seem to work.

As for transforming your query to use the NOT EXISTS, you didn't provide us
with the necessary details on the relationship between tbl1 and tbl2.

 

[Jerry Whittle]

SELECT *
FROM tbl1
WHERE Not Exists (SELECT * FROM tbl2
WHERE tbl2.ID = tbl1.ID
AND Something = SomethingElse);

But as Jeff says, it's better to see what isn't working. I usually only
switch from Exists to In's or back for performance reasons. Both, if properly
written, return the same records.

 

[John Spencer (MVP)]

A reason that Not IN can fail is if the subquery returns a null value. In
that case every record will be returned since every record when tested
against the NULL value will null (not true) and then the "Not True" will get
converted to True by the NOT.

IF that is the case you could change the subquery to

SELECT tbl2.Id from Tbl2 WHERE ... AND tbl2.ID is not null.

OR change the criteria and query to

WHERE NOT EXISTS (SELECT * FROM tbl2 WHERE ...)

Using the all field (*) will work since that returns the entire record and
exists will just check to see if there is a record and not worry about null
values. The problem is that you will probably have to make the sub-query a
correlated sub-query and reference some value or values in the main query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County