I used a SQL query similar to:
select * from FOO_TABLE ft where ft.foo_field != any ('A','B','C');
I thought that the result would be all records where foo_field didn’t contain ‘A’ or ‘B’ or ‘C’ (exclusive), but I obtained as result all records, also those with foo_field equal to ‘A’ or ‘B’ or ‘C’ .
To avoid the above issue, I used:
select * from FOO_TABLE ft where ft.foo_field not in ('A','B','C');
The above query runs as I expected.
I, often, use the following queries to generate the inverse of the queries above (to obtain all the records which contain ‘A’ or ‘B’ or ‘C ‘ – inclusive):
select * from FOO_TABLE ft where ft.foo_field = any ('A','B','C');
select * from FOO_TABLE ft where ft.foo_field in ('A','B','C');
I think that’s there’re no result differences between the two queries I use as inclusive. It’s that true!?
Why do the “exclusive” queries have different behaviour?
Advertisement
Answer
I think you are misusing NOT(!) operator.
How it works:
“column_name = ANY (…)”: The value must match one or more values in the list to evaluate to TRUE.
“column_name != ANY (…)”: The value must not match one or more values in the list to evaluate to TRUE.
In your case, your column value lets say 'A' is matching with =ANY('A','B','C') but at the same time when you use !=ANY('A','B','C') then also it will evaluate to TRUE as A!=B or A!=C.
So you must use column_name !=ALL('A','B','C') or use NOT column_name =ANY('A','B','C') as following:
Either use
select * from FOO_TABLE ft where NOT ft.foo_field = any ('A','B','C');
-- see the keyword NOT before column name
or
select * from FOO_TABLE ft where ft.foo_field != ALL ('A','B','C');
Cheers!!