Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected

Richard Broersma Jr wrote:

> Here is the example that doesn't do what I expect:
>
> --find all parents that have a mixture of boys and girls.
> --but this doesn't return anything
> SELECT *
>   FROM Parents AS P
>  WHERE 'girl' <> ALL ( SELECT gender
>                          FROM Children AS C1
>                         WHERE C1.parentid = P.parentid )
>    AND 'boy' <> ALL (  SELECT gender
>                          FROM Children AS C1
>                         WHERE C1.parentid = P.parentid );

I read this as: Find all parents such that none of their kids are
girls and none of their kids are boys.  That is, ALL of their genders
are <> 'girl', AND ALL of their genders are <> 'boy'.  Under the
obvious assumptions about gender, the result is of course empty -
except it's not clear to me what should happen for childless people ...

> --This query gets what I want
> --but I believe that it shouldn't return anything
>
> SELECT *
>   FROM Parents AS P
>  WHERE 'girl' <> ANY ( SELECT gender
>                          FROM Children AS C1
>                         WHERE C1.parentid = P.parentid )
>    AND 'boy' <> ANY (  SELECT gender
>                          FROM Children AS C1
>                         WHERE C1.parentid = P.parentid );

I read this as:  Find all parents such that at least one of their
kids is not a girl, and at least one of their kids is not a boy.  Of
course, this could also be written with =.

- John Burger
   MITRE