Null in subquery returns no records

[Mike MacSween]

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?

Yours, Mike MacSween

[Database Guy]

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<40**********************@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.

That is the SQL standard, oddly IMHO.
DG

[Lennart Jonsson]

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<40**********************@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

[Mike MacSween]

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" <le*****@kommunicera.umea.se> wrote in message
news:6d**************************@posting.google.c om...

"Mike MacSween" <mi******************@btinternet.com> wrote in message

news:<40**********************@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

[ben brugman]

"Lennart Jonsson" <le*****@kommunicera.umea.se> wrote in message >

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

Three valued logic is problematic, but I do not agree with the above.
Not all conditions containing a null evalutate to null, for example

False AND null evaluates to False

your line ... and null -> null does not 'respect' that rule.

For tree valued logic, the extra set above boolean logic is :

1. Not null --> null
2. false and null --> false
3. true and null --> null
4. false or null --> null
5. true or null --> true

If a complete condition evaluates to null in a 'complete' clause
it is handled as false, the 'concerning' row is not selected.

I know this is true for MS-SQL-server and for Oracle, I do not
know how this is implemented in other databases.

For Oracle (also MS-SQL-server if you create dual with one row).
null is generated by (null = null)
false and true are generated bij a simple compare.
0. select * from dual where (null = null)
1. select * from dual where not (null= null)
2. select * from dual where (1=0) and (null = null)
use select * from dual where not((1=0) and (null = null)) (Gives a line)
to see that 2 evaluates to false. not false --> true
3. select * from dual where (1=1) and (null = null)
4. select * from dual where (1=0) or (null = null)
5. select * from dual where (1=1) or (null = null) (Gives a line)

ben brugman
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

[Lennart Jonsson]

"ben brugman" <be*@niethier.nl> wrote in message news:<40**********************@read.news.nl.uu.net >...

"Lennart Jonsson" <le*****@kommunicera.umea.se> wrote in message >

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

Three valued logic is problematic, but I do not agree with the above.
Not all conditions containing a null evalutate to null, for example

False AND null evaluates to False

your line ... and null -> null does not 'respect' that rule.

In general youre right, but in the context given x!=y[i] evaluates to
true. I was a bit sloppy, and left out the last steps of the
reduction. What I meant was:

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

[...]

/Lennart