I have a table
ID myField ------------ 1 someValue 2 NULL 3 someOtherValue
and a T-SQL Boolean expression which can evaluate to TRUE, FALSE or (due to SQL's ternary logic) UNKNOWN:
SELECT * FROM myTable WHERE myField = 'someValue' -- yields record 1
If I want to get all the other records, I cannot simply negate the expression
SELECT * FROM myTable WHERE NOT (myField = 'someValue') -- yields only record 3
I know how why this happens (ternary logic), and I know how to solve this specific issue.
I know I can just use
myField = 'someValue' AND NOT myField IS NULL and I get an "invertible" expression which never yields UNKNOWN:
SELECT * FROM myTable WHERE NOT (myField = 'someValue' AND myField IS NOT NULL) -- yields records 2 and 3, hooray!
Now, let's talk about the general case. Let's say instead of
myField = 'someValue' I have some complex expression involving lots of fields and conditions, maybe subqueries:
SELECT * FROM myTable WHERE ...some complex Boolean expression...
Is there a generic way to "invert" this expession? Bonus points if it works for subexpressions:
SELECT * FROM myTable WHERE ...some expression which stays... AND ...some expression which I might want to invert...
I need to support SQL Server 2008-2014, but if there's an elegant solution requiring a newer version than 2008, I'm interested to hear about it too.