Negating an arbitrary where clause condition (including the null tests) Negating an arbitrary where clause condition (including the null tests) oracle oracle

Negating an arbitrary where clause condition (including the null tests)


Assuming your table has a primary key id, one possible approach is:

select count(*)from people p1left join people p2  on (p1.id = p2.id  and (p2.<condition A>)  and (p2.<contition B>))where p1.<condition A>  and p2.id IS NULL

You do need some simple preprocessing on the conditions (prefacing each column name with p1. or p2. as appropriate), but that's much easier than correctly negating conditions with the NULL issues you mention.

LEFT JOIN sometable ON whatever WHERE ... AND sometable.id IS NULL is a popular way to express "and there's no corresponding record in sometable that satisfied the whatever constraint, so I would expect a good engine to be well tuned to optimize that idiom as much as allowed by the available indices.


If for every nullable column you can come up with a dummy value that should never be valid, then you could do something like this:

select count(*) from dualwhere exists (  select * from (    select NVL(name, 'No Name') name, NVL(age, -1) age from people    )  where (<condition A>)  and not (<condition B>));

You would probably want to create function-based indexes on those expressions.

This is certainly easier than parsing through the conditions at runtime and trying to replace the column names with NVL expressions, and it should have the same end result.


If you do have the id field, try:

select count(*) from dualwhere exists (select * from peoplewhere (cond a)and zzz.id not in (select id from people where (cond b)));