Re: Null in subquery returns no records

Thanks for that.

It's clear now. This http://www.firstsql.com/iexist3.htm is also interesting. And the paragraph:

'Even when query developers carefully avoid any incorrect formulations, the error may occur anyway. Many RDBMSs routinely perform transformations of queries during their query optimization phases. For example, Date's original query using IN could be transformed into the EXISTS query during optimization. Normally, this is a correct transformation (Date used the transformation since it is theoretically correct), but because of the error in the definition of WHERE in ANSI SQL it changes a correct query into an incorrect one. Even ostensibly correct queries can produce wrong results when subqueries are used.'

is worrying.

New testing rule for Mike - always test with null values.

Yours, Mike MacSween

"Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote in message news:6dae7e65.0402062311.2ca605f5_at_posting.google.com...
> "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:39:54 CET