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

On Oct 10, 2007, at 12:38 PM, Richard Broersma Jr wrote:

> I expect that my intuition is incorrect about the use of ALL() and
> ANY(), but I found my result to
> be reverse from what I expected.
>
> Can anyone explain why <> ANY() behaves that way it does?
>
> Here are two test case examples that do what I expect:
>
> -- find all parent that only have boys
> SELECT *
>   FROM Parents AS P
>  WHERE 'boy' = ALL ( SELECT gender
>                        FROM Children AS C1
>                       WHERE C1.parentid = P.parentid );
>
> -- find all parent that only have girls
> SELECT *
>   FROM Parents AS P
>  WHERE 'girl' = ALL ( SELECT gender
>                         FROM Children AS C1
>                        WHERE C1.parentid = P.parentid );
>
>
> 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 );

This says "Give me all parents for which it is true that all of their
children are not girls and all children are not boys" which will only
be true for parents with no children.  Add a record to your Parents
table without any corresponding Children record(s) and you'll get a
result.

>
> --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 );

This is correct.  It reads "Give me all parents for whom any of their
children is not a girl AND any of their children is not a boy."  So,
for a parent with both a boy and a girl, the boy is not a girl and
the girl is not a boy.  You could replace the <> ANY with a simple IN
as then it would be "Give me all parents who have both a boy and a
girl."

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com