'Not In' query not seem to be working

[John]

Hi

Access 2000 SP3. I have a Clients table which has 5400 records.When I run
the below query to return all records in Clients table that have matching
keys in Contacts table then I get 4047 records.

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE
(((Contacts.[Company Type])="C")))));

When I run the below query to return all records in Clients table that DO
NOT have matching keys in Contacts table then I expect to get 1353
(5400-4047) records;

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts
WHERE (((Contacts.[Company Type])="C")))));

However I get 0 (nill) records. What is the problem and how can I make the
query work to return all records in Clients table that do not have any
matches in Contacts table?

Thanks

Regards

 

[pietlinden]

Hi

Access 2000 SP3. I have a Clients table which has 5400 records.When I run
the below query to return all records in Clients table that have matching
keys in Contacts table then I get 4047 records.

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE
(((Contacts.[Company Type])="C")))));

When I run the below query to return all records in Clients table that DO
NOT have matching keys in Contacts table then I expect  to get 1353
(5400-4047) records;

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts
WHERE (((Contacts.[Company Type])="C")))));

However I get 0 (nill) records. What is the problem and how can I make the
query work to return all records in Clients table that do not have any
matches in Contacts table?

Thanks

Regards

using NOT IN with a subselect is going to have hideous performance
with large datasets. Use an outer join instead. Use the Find
Unmatched query wizard, and the wizard will build the query for you.

something like...
SELECT Clients.ID, Clients.Company
FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company
ID]
WHERE Contacts.[Company Type]="C"
AND Contacts.[Company ID] IS NULL;

 

[John]

Hi

Thanks. Did that and worked. I wasn't too worried about the performance as
it is an adhoc query but its still puzzling why Not In version did not work.

Thanks again.

Regards

Hi

Access 2000 SP3. I have a Clients table which has 5400 records.When I run
the below query to return all records in Clients table that have matching
keys in Contacts table then I get 4047 records.

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE
(((Contacts.[Company Type])="C")))));

When I run the below query to return all records in Clients table that DO
NOT have matching keys in Contacts table then I expect to get 1353
(5400-4047) records;

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts
WHERE (((Contacts.[Company Type])="C")))));

However I get 0 (nill) records. What is the problem and how can I make the
query work to return all records in Clients table that do not have any
matches in Contacts table?

Thanks

Regards

using NOT IN with a subselect is going to have hideous performance
with large datasets. Use an outer join instead. Use the Find
Unmatched query wizard, and the wizard will build the query for you.

something like...
SELECT Clients.ID, Clients.Company
FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company
ID]
WHERE Contacts.[Company Type]="C"
AND Contacts.[Company ID] IS NULL;

 

[Michel Walsh]

NOT IN( SELECT... ) where SELECT return a NULL among other records is a
condition that can never be TRUE, so no record are returned:


x NOT IN (a, b, c)
is the same as
x <> a AND x <> b AND x <> c

now, if a is null, that solves to

NULL AND ...

wich always solve to either NULL, with FALSE (never to TRUE).


Note that JET removes the NULL if the list is constant. It is only in NOT
IN(SELECT ... ) that you get the effect.


Vanderghast, Access MVP

Hi

Thanks. Did that and worked. I wasn't too worried about the performance as
it is an adhoc query but its still puzzling why Not In version did not
work.

Thanks again.

Regards

Hi

Access 2000 SP3. I have a Clients table which has 5400 records.When I run
the below query to return all records in Clients table that have matching
keys in Contacts table then I get 4047 records.

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE
(((Contacts.[Company Type])="C")))));

When I run the below query to return all records in Clients table that DO
NOT have matching keys in Contacts table then I expect to get 1353
(5400-4047) records;

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts
WHERE (((Contacts.[Company Type])="C")))));

However I get 0 (nill) records. What is the problem and how can I make
the
query work to return all records in Clients table that do not have any
matches in Contacts table?

Thanks

Regards

using NOT IN with a subselect is going to have hideous performance
with large datasets. Use an outer join instead. Use the Find
Unmatched query wizard, and the wizard will build the query for you.

something like...
SELECT Clients.ID, Clients.Company
FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company
ID]
WHERE Contacts.[Company Type]="C"
AND Contacts.[Company ID] IS NULL;

 

[Ken Sheridan]

I don't see that what MW says can apply in your case as it would mean that no
rows would be returned whether you use the IN or NOT IN predicates as the
subquery is exactly the same in each case. Also what he says does not match
my own experience.

I can see no obvious explanation for the behaviour you are experiencing. I
think you can probably exclude the [Company Type]="C" criterion from the
subquery's WHERE clause, but the NOT IN predicate should have worked as you
expect even so. Better than the NOT IN predicate, however, would be to use
the NOT EXISTS predicate:

SELECT *
FROM Clients
WHERE NOT EXISTS
(SELECT *
FROM Contacts
WHERE Contacts.[Company ID] = Clients.ID);

You could of course leave the [Company Type]="C" criterion in the above
subquery and it should still work, but it looks to me like its unnecessary.

As regards the solution by PL, I can only assume that you must have omitted
the [Company Type] ="C" criterion for it to work, in which case it’s the best
solution, though its usual to examine the primary key column of the table on
the outer side of the join for IS NULL, not the foreign key. Otherwise, as
posted with the inclusion of the [Company Type] ="C" criterion, it should
return zero rows. This is because with that criterion included it restricts
the query on a column in the table on the outer side of the left outer join.
An outer join can only be restricted on a column in the table on the inner
side. This makes sense, as if you think about the logic underlying an outer
join, you can’t return rows from one table on the basis a criterion based on
values in rows in another table which don't actually exist. In fact all it
does is cause the query to behave as if the join is an INNER JOIN.

If you have used the query exactly as posted by PL and it does return rows,
then it would appear to be defying logic! I'd be very interested to know if
this is the case, but unfortunately I'll be away incommunicado from now for a
while, so my apologies in advance if you do respond to this post and don't
hear back from me. Bearing in mind the inexplicable behaviour you are
experiencing in use of the IN or NOT IN predicates and a subquery, however,
nothing would surprise me.

I do wonder whether there is something about the design of the tables which
might explain the strange behaviour. I notice that you have a Company Type
column in Contacts, but that would seem to me to be an attribute of Clients
rather than Contacts, judging by the fact that the latter includes a Company
column.

Ken Sheridan
Stafford, England

Hi

Thanks. Did that and worked. I wasn't too worried about the performance as
it is an adhoc query but its still puzzling why Not In version did not work.

Thanks again.

Regards

Hi

Access 2000 SP3. I have a Clients table which has 5400 records.When I run
the below query to return all records in Clients table that have matching
keys in Contacts table then I get 4047 records.

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE
(((Contacts.[Company Type])="C")))));

When I run the below query to return all records in Clients table that DO
NOT have matching keys in Contacts table then I expect to get 1353
(5400-4047) records;

SELECT Clients.ID, Clients.Company
FROM Clients
WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts
WHERE (((Contacts.[Company Type])="C")))));

However I get 0 (nill) records. What is the problem and how can I make the
query work to return all records in Clients table that do not have any
matches in Contacts table?

Thanks

Regards

using NOT IN with a subselect is going to have hideous performance
with large datasets. Use an outer join instead. Use the Find
Unmatched query wizard, and the wizard will build the query for you.

something like...
SELECT Clients.ID, Clients.Company
FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company
ID]
WHERE Contacts.[Company Type]="C"
AND Contacts.[Company ID] IS NULL;

 

[Michel Walsh]

The OP does not ask for a better solution, just why the NOT IN does not
work. Too bad you don't understand the explanation I supplied, since that is
the case, and an ELEMENTARY case. Probably my fault, though, I mean,
probably I was not clear enough. So



IN (SELECT ... )

is totally different. Indeed if we consider

x IN ( a , b, c)


it is the same as


x=a OR x=b OR x=c


Dealing with OR, if a is null and x is equal to b, that returns TRUE:


null OR true OR whatever


returns TRUE.


It is a different story with NOT IN since NOT IN implies AND conjunction,
not OR, and so, NOT IN with one of the elements being a NULL NEVER returns
TRUE..




FURTHERMORE, what is better than A SIMPLE TEST, in NORTHWIND:


----------------------------------
SELECT Employees.LastName
FROM Employees
WHERE (((Employees.LastName) Not In (SELECT iif(lastName="King", null,
LastName) FROM employees)));
------------------------------------



which returns nothing and


--------------------------------------
SELECT Employees.LastName
FROM Employees
WHERE (((Employees.LastName) In (SELECT iif(lastName="King", null,
LastName) FROM employees)));
--------------------------------------

which returns all records, except the one for King, Robert.




A simple SOLUTION is to add a where clause in the subquery:


SELECT ...
FROM ...
WHERE fieldName NOT IN( SELECT otherField ... WHERE NOT( otherFIeld IS
NULL) )




Again, with Jet, that is applicable only with NOT IN( SELECT ... ) since
with NOT IN( list of constant), Jet removes the NULL in the list, for that
case.



Vanderghast, Access MVP

 

[Ken Sheridan]

It is quite true that I had misread your original reply, and thought that you
were ascribing the behaviour both to the IN and NOT IN predicates. My fault,
not yours. I see now that it was to the latter, and in that context the
behaviour is of course as you say. If the OP is still with us I think it
would perhaps contribute to their better understanding of the logical
position to expand your explanation a little:

x IN ( a , b, c)

does, as you say, equate to:

x=a OR x=b OR x=c

but:

x NOT IN ( a , b, c)

firstly can be equated to:

NOT (x IN (a, b, c))

and thence to:

NOT(x=a OR x=b OR x=c)

From this, applying DeMorgan's laws, we arrive at your:

x<>a AND x<>=b AND x<>=c

which, as you say, explains the behaviour.

However, I do strongly disagree with the implication in your opening
sentence that one should restrict one's answers to the precise question
asked. If a better solution to a stated aim exists then I think it is
incumbent on us draw this to the questioner's attention. Generally speaking
the EXISTS predicate will give better performance than the IN predicate. This
too has its problems with NULLs, but not in the context of the OP's question,
I think.

While this logical navel gazing is very interesting what seems to me to be
of more importance from the OP's point of view is the implication of the fact
that PL's solution, while apparently flawed, seems to have given the correct
result. If used as posted it does tend to suggest that there is something
not quite right in the logical model.

Ken Sheridan
Stafford, England