Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
2  
We can make a Connect item (if there isn't one) for Microsoft to add the (standard) IS DISTINCT FROM operator. – ypercubeᵀᴹ Mar 21 at 16:43
2  
@ypercubeᵀᴹ: There is one already, with (currently) 277 upvotes. – Heinzi Mar 21 at 16:57

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
;

UPDATE

Since SQL Server 2012+ you 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.

share|improve this answer
    
That's a nice idea! Use CASE to "convert" a Boolean expression to an expression one can work with, and then use a comparison to "convert" it back to a Boolean expression. – Heinzi Mar 21 at 16:33

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:

result

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

Returns:

Negated result

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)
    ;
share|improve this answer

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>
share|improve this answer

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.

share|improve this answer
    
COALESCE(myField, CONCAT('not', 'someValue')) = 'someValue' should work for any "someValue" and any data in the table. – JimmyB Mar 22 at 12:03

Is COALESCE available?

SELECT * FROM myTable WHERE NOT COALESCE(myField = 'someValue', FALSE)
share|improve this answer
4  
Yes, COALESCE is available, but no, this won't work: (a) COALESCE won't accept a Boolean expression (neither will ISNULL, by the way) and (b) the truth value FALSE is not directly available in SQL as a literal. Try it, and you'll get a syntax error. – Heinzi Mar 21 at 21:33
    
@Heinzi -- I did try it, it worked, that's why I posted it. Maybe it doesn't work on T-SQL, but it's fine on Postgres and MySQL. – Malvolio Mar 21 at 23:25
2  
@Malvolio: The question is tagged sql-server, though, not mysql or postgresql. – Andriy M Mar 22 at 6:19
    
@Malvolio that's because Postgres has a BOOLEAN type and MySQL has a (faked) BOOLEAN type which can be parameters of the COALESCE() function. If the question had been tagged with sql-agnostic or sql-standard, the answer would be ok. – ypercubeᵀᴹ Mar 22 at 7:22
    
@ypercubeᵀᴹ -- eh, what can I tell you? Get a better database. – Malvolio Mar 23 at 2:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.