SQL "IN" operator

  • Thread starter Thread starter JimS
  • Start date Start date
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.
 
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
..
 
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...
 
Back
Top