<> 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Erik Jones
Дата:
Сообщение: Re: disjoint union types
Следующее
От: "John D. Burger"
Дата:
Сообщение: Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected