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 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, privacy policy and cookie policy

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