Select where bit is not 0

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?

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';

StackExchange Database Administrators Q#102720, answer score: 3