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
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.