Problem in WHERE clause

So I have a query. It works if I comment out the part checking for the exclusion.

WHERE H.BatchId = 3  AND H.IsExcluded != 1

IsExcluded is a bit field that accepts NULL values.

All the values I'm selecting from are NULL, so that SHOULD be right. What am I doing wrong? It's returning no values the way开发者_开发技巧 it is, but if I comment out just the 'AND' and after, it works.

WHERE H.BatchId = 3  AND (H.IsExcluded != 1 OR H.IsExcluded IS NULL)

In SQL NULL != NULL as @scott said but you may use SET ANSI_NULLS OFF.

Declare @a INT = NULL
Declare @b INT = NULL
SET ANSI_NULLS OFF
IF(@b = @a)
    PRINT 'NULL is equal to NULL'
ELSE
    PRINT 'NULL is not equal to  NULL'

SET ANSI_NULLS ON
IF(@b = @a)
    PRINT 'NULL is equal to NULL'
ELSE
    PRINT 'NULL is not equal to  NULL'

Output is:

• NULL is equal to NULL: ANSI_NULLS set to OFF
• NULL is not equal to NULL: ANSI_NULLS set to ON, this is the default SQL Server behaviour.