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