Invert a Boolean expression which can return UNKNOWN

Example

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!

General Case

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.

You could enclose the condition in a CASE expression that returns a binary result, for instance 1 or 0:

SELECT
  ...
FROM
  ...
WHERE
  CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
;

Negating the expression will give you all the other rows from the same data source, including those where someColumn is null:

SELECT
  ...
FROM
  ...
WHERE
  NOT CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
  -- or: CASE WHEN someColumn = someValue THEN 1 ELSE 0 END <> 1
;

Since SQL Server 2012 you also have the IIF function, which is just a wrapper around a binary CASE like above. So, this CASE expression:

CASE WHEN someColumn = someValue THEN 1 ELSE 0 END

will look like this if rewritten using IIF:

IIF(someColumn = someValue, 1, 0)

And you can use it exactly the same way as the CASE expression. There will be no difference in performance, only the code will be slightly more concise, possibly cleaner that way too.

StackExchange Database Administrators Q#132874, answer score: 16