<> ALL( ... ) and <> ANY ( ... ) didn't behave as expected
| От | Richard Broersma Jr |
|---|---|
| Тема | <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected |
| Дата | |
| Msg-id | 527383.60689.qm@web31801.mail.mud.yahoo.com обсуждение исходный текст |
| Ответы |
Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected
Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected |
| Список | 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.
В списке pgsql-general по дате отправления: