16

In the WHERE clause of a SQL query I would expect these two conditions to have the same behavior:

NOT (a=1 AND b=1)

vs

a<>1 AND b<>1

The first condition behaves as expected, and while I epxect the second condition to do the same thing, it does not.

This is very basic stuff, but ashamedly I can't see what I'm doing wrong.

  • Can you post example data and expected results vs. actual results? – Gareth Lyons Apr 6 '17 at 10:16
  • 6
    As noted by Lenard in his answer, this is an example of De Morgan rules: not (A and B) = (not A) or (not B), not (A or B) = (not A) and (not B). Be careful with NULL values. – Barranka Apr 7 '17 at 3:10
  • 2
    Just think of it in English. Your first is "It's not the case that I'm both the King of France and also human" - eminently true. Your second is "I'm neither the King of France nor human" - eminently false. – Patrick Stevens Apr 7 '17 at 7:38
  • 3
    This conflicts with "De Morgan's law". The equivalent would be a <> 1 OR b<>1. – Willem Van Onsem Apr 7 '17 at 13:47
46

They are not equivalent.

NOT (a=1 AND b=1)

is equivalent with:

(NOT a=1 OR NOT b=1) <=> (a<>1 OR b<>1)

This equivalence is known as De Morgan's Law. See for example:

https://en.wikipedia.org/wiki/De_Morgan%27s_laws

A nice technique for proving/disproving equivalences for boolean algebra expressions is to use a cte for the domains, and compare the expressions side by side:

with T(a) as ( values 0,1 )
   , U(a,b) as (select t1.a, t2.a as b 
               from t as t1 
               cross join t as t2
) 
select a,b
    , case when not (a=1 and b=1) then 1 else 0 end
    , case when a<>1 and b<>1 then 1 else 0 end 
from U

A           B           3           4          
----------- ----------- ----------- -----------
          0           0           1           1
          0           1           1           0
          1           0           1           0
          1           1           0           0

Edit: Since DB2 does not support Boolean datatype I expanded the example at:

http://sqlfiddle.com/#!15/25e1a/19

The rewritten query looks like:

with T(a) as ( values (0),(1),(null) )
   , U(a,b) as (select t1.a, t2.a as b 
                from t as t1 
                cross join t as t2
) 
select a,b
     , not (a=1 and b=1) as exp1 
     , a<>1 or b<>1 as exp2
from U;

The result of the query is:

a       b       exp1        exp2
--------------------------------
0       0       true        true
0       1       true        true
0       (null)  true        true
1       0       true        true
1       1       false       false
1       (null)  (null)      (null)
(null)  0       true        true
(null)  1       (null)      (null)
(null)  (null)  (null)      (null)

As shown exp1 and exp2 are equivalent.

  • 16
    +1 just for mentioning De Morgan. Should be required reading for anyone doing any form of programming/scripting. – Tonny Apr 6 '17 at 12:14
  • But what about NULL? – dan04 Apr 6 '17 at 14:11
  • @dan04 You can add NULL to the first line (becomes with T(a) as ( values 0,1,NULL ) and re-run the query and you'll see what happens. NULLs definitely throw a wrench in most of the set equivalency rules we learn. Short answer is a=NULL and a<>NULL both yield NULL, so they will fall to the else case. For further reading: (stackoverflow.com/questions/1833949/…) – Brian J Apr 6 '17 at 15:07
  • I am unsure why you had to amend the first example for DB2. It works as shown for me. I am using DB2 for i rather than DB2 LUW. The second example has some syntax errors for DB2 for i. – jmarkmurphy Apr 7 '17 at 12:22
  • @jmarkmurphy, I don't know DB2 for i, perhaps it works there. For LUW the case expression maps to either 0 or 1 so that have to be changed to include null as well. By doing so the case expression is no longer trivial (IMO), and the expressions become difficult to reason about. – Lennart Apr 7 '17 at 15:40
9

Your first example is saying:

Return all rows except where both a = 1 AND b = 1

Your second example is saying:

Return all rows except where either a = 1 OR b = 1

For the second query to return the same as the first, you should change your AND to an OR

CREATE TABLE #Test (a BIT, b BIT);

INSERT INTO #Test
        ( a, b )
VALUES
        ( 0, 0 ),
        ( 1, 0 ),
        ( 0, 1 ),
        ( 1, 1 );

SELECT * FROM #Test AS t
WHERE NOT (a=1 AND b=1);

SELECT * FROM #Test AS t
WHERE (a <> 1 OR b <> 1);

This returns the following results

a   b
0   0
1   0
0   1
  • Could you describe why a<>1 AND b<>1 translates to "either a=1 OR b=1"? – doub1ejack Apr 7 '17 at 11:36
  • 1
    @doub1ejack, you need an additional negation in your second statement to make it equivalent with the first: NOT ( a=1 OR b=1 ). Unfortunate natural languages contain ambiguities which makes it difficult to translate logical formulas to natural languages and vice versa. For example, does neither a=1 nor b=1 mean NOT ( a=1 OR b=1 ) or (NOT a=1) OR (NOT b=1)? – Lennart Apr 7 '17 at 11:48
  • 1
    @doub1ejack The opposite of "the car is red AND has four doors" is "Either the car isn't red, OR it doesn't have four doors." If multiple things have to be true to make a statement true, then only one of them has to be false to make it false. – hobbs Apr 7 '17 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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