Re: NOT IN operator not working with subquery.
From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 10/27/04
- Next message: Jamie Collins: "Re: SQL Specific Window for numeric data types?"
- Previous message: Allen Browne: "Re: SQL Specific Window for numeric data types?"
- In reply to: Terry: "NOT IN operator not working with subquery."
- Next in thread: Terry: "Re: NOT IN operator not working with subquery."
- Reply: Terry: "Re: NOT IN operator not working with subquery."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 27 Oct 2004 06:18:02 -0400
Hi,
It does not return any record? Probably there is a NULL in values returned
by the SELECT. Here is why.
x IN( a, b, c)
is the same as
x=a OR x=b OR x=c
x NOT IN(a, b, c)
is thus the same as
x <> a AND x <> b AND x <> c
If "c" is NULL, x <> NULL returns null, and thus, the last AND sequence
returns NULL. Now,
WHERE NULL
does not accept the record. Since that is the case for all records, all the
records are rejected.
In summary,
WHERE x NOT IN( SELECT ... )
will not return ANY record if the SELECT returns a NULL. With JET, if the
list is constant, rather than coming from a SELECT, the NULL is NOT
considered when the evaluation is performed.
? eval( " 4 NOT IN( NULL , 5, 6) " )
-1
but the "behavior" stands for a IN list from a SELECT.
Hoping it may help,
Vanderghast, Access MVP
"Terry" <ttrapp.spam.me.not@org.insurors> wrote in message
news:U0zfd.38$YE4.7@fe61.usenetserver.com...
>I want to get a list of company names that we have in our database, that we
> have not ever sent an invoice to. Why does the following query not work? I
> have been banging my head on this for about an hour and can't figure it
> out.
> Any help would be much appreciated.
>
> SELECT [Companyinfo].[Name], [Companyinfo].[ID#] FROM [Companyinfo] WHERE
> [Companyinfo].[ID#] NOT IN (SELECT [Link ID] FROM [Invoices]);
>
> [Invoices].[Link ID] points to [Companyinfo].[ID#]
>
> -- Terry
>
>
>
- Next message: Jamie Collins: "Re: SQL Specific Window for numeric data types?"
- Previous message: Allen Browne: "Re: SQL Specific Window for numeric data types?"
- In reply to: Terry: "NOT IN operator not working with subquery."
- Next in thread: Terry: "Re: NOT IN operator not working with subquery."
- Reply: Terry: "Re: NOT IN operator not working with subquery."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
