Look back to formal logic and algebra. An expression like
A & B & (D | E)
may be negated in a couple of ways:
The obvious way:
!( A & B & ( D | E ) )
The above can also be restated, you just need to remember some properties of logical expressions:
!( A & B ) is the equivalent of (!A | !B).!( A | B ) is the equivalent of (!A & !B).!( !A ) is the equivalent of (A).Distribute the NOT (!) across the entire expression to which it applies, inverting operators and eliminating double negatives as you go along:
!A | !B | ( !D & !E )
So, in general, any where clause may be negated according to the above rules. The negation of this
select *
from foo
where test-1
and test-2
and ( test-3
OR test-4
)
is
select *
from foo
where NOT( test-1
and test-2
and ( test-3
OR test-4
)
)
or
select *
from foo
where not test-1
OR not test-2
OR ( not test-3
and not test-4
)
Which is better? That's a very context-sensitive question. Only you can decide that.
Be aware, though, that the use of NOT can affect what the optimizer can or can't do. You might get a less than optimal query plan.