Invert a Boolean expression which can return UNKNOWN

Question

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.

Answer 1

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.

Answer 2

The first thought that occurs to me:

DECLARE @T AS table (c1 integer NULL);

INSERT @T (c1)
VALUES (1), (NULL), (2);

-- Original expression c1 = 1
SELECT T.c1
FROM @T AS T
WHERE c1 = 1;

Returns:

-- Negated
SELECT T.c1
FROM @T AS T
WHERE NOT EXISTS (SELECT 1 WHERE c1 = 1);

Returns:

This relies on the way EXISTS always returns true or false, never unknown. The need for the SELECT 1 WHERE is unfortunately necessary, but it could be workable for your requirement, for example:

sql = "
    SELECT * 
    FROM someTable 
    WHERE " + someExpression + 
    " AND NOT EXISTS (SELECT 1 WHERE " + 
    someOtherExpression + ")";
result = executeAndShow(sql);

See EXISTS (Transact-SQL)

---

A slightly more complex worked example showing how either EXISTS or CASE/IIF methods might be applied to invert individual predicates:

DECLARE @T AS table 
(
    c1 integer NULL,
    c2 integer NULL,
    c3 integer NULL
);

INSERT @T 
    (c1, c2, c3)
VALUES 
    (1, NULL, 2),
    (2, 2, 3),
    (NULL, 1, 4);

Code:

-- Original
SELECT 
    T.c1,
    T.c2,
    T.c3
FROM @T AS T
WHERE
    1 = 1
    -- Predicate #1
    AND T.c1 = 2
    -- Predicate #2
    AND T.c2 =
    (
        SELECT MAX(T2.c2)
        FROM @T AS T2
        WHERE T2.c2 IS NOT NULL
    )
    -- Predicate #3
    AND T.c3 IN (3, 4)
    ;

-- Invert predicates #1 and #2
SELECT 
    T.c1,
    T.c2,
    T.c3
FROM @T AS T
WHERE
    1 = 1
    AND NOT EXISTS (SELECT 1 WHERE 1 = 1
        -- Predicate #1
        AND T.c1 = 2)
    AND NOT EXISTS (SELECT 1 WHERE 1 = 1
        -- Predicate #2
            AND T.c2 =
            (
                SELECT MAX(T2.c2)
                FROM @T AS T2
                WHERE T2.c2 IS NOT NULL
            ))
    -- Predicate #3
    AND T.c3 IN (3, 4)
    ;

Answer 3

If you don't mind rewriting the sub-expressions up front, you can use COALESCE:

SELECT *
FROM myTable
WHERE NOT (COALESCE(myField, 'notSomeValue') = 'someValue')

You must make sure that 'notSomeValue' is distinct from 'someValue'; preferably, it would be some completely illegal value for the column. (It can't be NULL, either, of course.) This is easy to negate, even if you have a long list:

SELECT *
FROM myTable
WHERE NOT (
    COALESCE(myField, 'notSomeValue') = 'someValue' AND
    COALESCE(myField2, 'notSomeValue') = 'someValue2' AND
    COALESCE(myField3, 'notSomeValue') = 'someValue3' AND
    COALESCE(myField4, 'notSomeValue') = 'someValue4'
)

Cleaner, simpler, and more obvious than CASE or IIF, in my opinion. The main downside is having a second value you know is not equal, but this is only really a problem if you don't know the actual value up front. In that case, you can do as Hanno Binder suggests and use COALESCE(myField, CONCAT('not', 'someValue')) = 'someValue' (where 'someValue' would actually be parameterized).

COALESCE is documented to be available from SQL Server 2005 onward.

Be aware that messing with your query like this (using any of the methods recommended here) can make it more difficult for the database to optimize your query. For large datasets, the IS NULL version is likely easier to optimize.

Answer 4

There's the built-in EXCEPT set operator which, effectively, removes a second query's results from the first query's.

select * from table
except
select * from table
where <really complex predicates>