Thread: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected
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 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 );
Regards,
Richard Broersma Jr.
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
Richard Broersma Jr wrote: > Here is the example that doesn't do what I expect: I'm guessing you're just stood too close to the problem. > --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 translates to WHERE <none of the children are girls> AND <none of the children are boys> Assuming you have a two-state gender then that's nothing. For 'girl' <> ALL (...) then all the values you test must be not girls. > --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 ); Translates to: WHERE <any child is not a girl> AND <any child is not a boy> So - at least one of each. -- Richard Huxton Archonet Ltd
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
Richard Broersma Jr <rabroersma@yahoo.com> writes:
> Can anyone explain why <> ANY() behaves that way it does?
Aside from the responses already given, I'm wondering whether you have
any NULL entries in Children.gender. NULL rows within a NOT IN
subselect tend to produce confusing results ...
regards, tom lane
--- "John D. Burger" <john@mitre.org> wrote: > 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 ... Thanks everyone that makes sense! Regards, Richard Broersma Jr.