SQL "IN" operator

J

JimS

I want to present a list of possible FKs in my where clause. That's simple
enough:

....WHERE FK IN (1,2,5,7)...

How do I include "NULL" values of the list? FK is a foreign key, and may be
null if there is no matching row in the foreign table...

I want the "null" selection, if possible, to stay within the "IN" clause for
programming convenience. If my only solution is to include an "ISNULL(FK) AND
FK IN (1,2...)" I will, but I'd prefer it in the list.
 
J

John Spencer

You cannot stick Null into the In clause. The In operator is just a
convenient short cut for writing a long series of OR clauses and as such
using NULL would effectively give you
Where [SomeField] = Null
AND nothing is ever equal to Null, not even Null.

So your criteria should be:

Where (FK In (1,2,5,7) or FK is Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

JimS

nuts.
Thanks John. As always, your answers are extremely helpful and greatly
appreciated, even when they aren't the answer I want to hear...

Thanks again.
--
Jim


John Spencer said:
You cannot stick Null into the In clause. The In operator is just a
convenient short cut for writing a long series of OR clauses and as such
using NULL would effectively give you
Where [SomeField] = Null
AND nothing is ever equal to Null, not even Null.

So your criteria should be:

Where (FK In (1,2,5,7) or FK is Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

JimS said:
I want to present a list of possible FKs in my where clause. That's simple
enough:

...WHERE FK IN (1,2,5,7)...

How do I include "NULL" values of the list? FK is a foreign key, and may
be
null if there is no matching row in the foreign table...

I want the "null" selection, if possible, to stay within the "IN" clause
for
programming convenience. If my only solution is to include an "ISNULL(FK)
AND
FK IN (1,2...)" I will, but I'd prefer it in the list.
Click to expand...
Click to expand...
 
Top