From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected |
Date: | 2007-10-10 17:38:39 |
Message-ID: | 527383.60689.qm@web31801.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John D. Burger | 2007-10-10 18:12:07 | Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected |
Previous Message | Erik Jones | 2007-10-10 17:10:10 | Re: disjoint union types |