3

I have made the following query to select a record that isn't 0.

SELECT * FROM Table WHERE Bit != 1 OR Bit IS NULL;

Why is the check Bit != 1 not enough for a nullable BIT?

1
  • 2
    where bool is distinct from true would be ANSI SQL
    – user1822
    Commented May 29, 2015 at 5:45

1 Answer 1

3

See following results:

  • 'true' != 'true' → false
  • 'false' != 'true' → true
  • NULL != 'true' → null (and equivalent to false in WHERE because it is not true)

What you can do is use COALESCE to change null into 'false'

SELECT * FROM Table WHERE COALESCE(Bool, 'false') != 'true';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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