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

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.

Responses

Browse pgsql-general by date

  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