sqlpostgresqlany

ANY and ALL against a small data set producing confusing results


This is a breakdown of the data set and the four queries. The production code uses != ANY in the query and produces a result that causes a full result set to be returned which is not the desired result rather the RHS Array should limit the return values to those in the ARRAY['K319','K318'].

Data Set:

select machine_name from fdr.machine_info;
machine_name
K318
K319
K320
K321
select machine_name from fdr.machine_info 
where machine_name = ANY (ARRAY['K319','K318']);
machine_name
K318
K319
select machine_name from fdr.machine_info 
where machine_name != ANY (ARRAY['K319','K318']);
machine_name
K318
K319
K320
K321
select machine_name from fdr.machine_info 
where machine_name = ALL (ARRAY['K319','K318']);
machine_name
select machine_name from fdr.machine_info 
where machine_name != ALL (ARRAY['K319','K318']);
machine_name
K320
K321

The correct result set is achieved if != ALL is used i.e. the result set is limited by the RHS ARRAY content. Having read the Postgres manual it is not clear why these results are as they are. It seems a simple query but with unexpected results. I prepare to be told it's too simple to bother with.


Solution

  • A <> ANY B means “there is an element in B that is different from A”.

    If B has two different elements, that is always true, because A cannot be equal to both at the same time. This comparison doesn't make a lot of sense.

    A <> ALL B means “there all elements in B are different from A”.

    That makes much more sense: you are looking for A that don't appear in B.