Re: Null in subquery returns no records

"Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message news:<4023985a$0$4096$5a6aecb4_at_news.aaisp.net.uk>...
> This as the row source for a combo:
>
> SELECT qryRole.RoleID, qryRole.Role
> FROM qryRole
> WHERE (((qryRole.RoleID) Not In (SELECT RoleID FROM qryRoleEvent INNER JOIN
> qryEvent ON qryRoleEvent.EventID = qryEvent.EventID WHERE ProdID =
> Forms!frmProductions!ProdID)))
> ORDER BY qryRole.Role;
>
> If there is just one RoleID with a null value in the subquery then the main
> query returns no records atall.
>
> This is needed:
>
> SELECT qryRole.RoleID, qryRole.Role
> FROM qryRole
> WHERE (((qryRole.RoleID) Not In (SELECT RoleID FROM qryRoleEvent INNER JOIN
> qryEvent ON qryRoleEvent.EventID = qryEvent.EventID WHERE ProdID =
> Forms!frmProductions!ProdID AND RoleID IS NOT NULL)))
> ORDER BY qryRole.Role;
>
> Why?

Think of null as "has an unknown value". If one or more RoleId's are unknown we can not for certain say that a specific roleid is not in the set. Another way of thinking about it is to transform the inner query as:

x not in (y1, y2, ..., null) ->
not (x=y1 or x=y2 or ... x=null) ->
x!=y1 and x!=y2 and ... x!=null ->
... and null ->
null

null is problematic in sql, and there have been lots of discussions about it. See for example:

http://www.firstsql.com/iexist2.htm

HTH
/Lennart Received on Sat Feb 07 2004 - 08:11:00 CET