MS SQL Server Unexpected Behavior-CodePudding

I thought I understood SQL fairly well, but here is a simple example of NULL values introducing a bug that I didn't expect. Could someone explain why the first and second SELECT queries yield different results.

DROP TABLE IF EXISTS #temp;

CREATE TABLE #temp
(
    ITEM int,
    RELATIONSHIP nvarchar(max) NULL
)

INSERT INTO #temp VALUES (1, '')
INSERT INTO #temp VALUES (2, NULL)
INSERT INTO #temp VALUES (3, 'Parent')
INSERT INTO #temp VALUES (4, 'Child')


SELECT 
    ITEM, RELATIONSHIP, 
    CAST(CASE 
             WHEN (RELATIONSHIP != 'Parent' AND RELATIONSHIP != 'Child') 
                 THEN 0 
                 ELSE 1 
         END AS bit) family 
FROM 
     #temp;

SELECT 
    ITEM, RELATIONSHIP, 
    CAST(CASE 
             WHEN (RELATIONSHIP = 'Parent' OR RELATIONSHIP = 'Child') 
                 THEN 1 
                 ELSE 0 
         END AS bit) family 
FROM 
    #temp;

SELECT 
    ITEM, RELATIONSHIP, 
    CAST(CASE 
             WHEN (RELATIONSHIP IS NULL OR (RELATIONSHIP != 'Parent' AND RELATIONSHIP != 'Child')) 
                 THEN 0 
                 ELSE 1 
         END AS bit) family 
FROM 
    #temp;

ITEM    RELATIONSHIP    family
1                       0
2       NULL            1
3       Parent          1
4       Child           1

ITEM    RELATIONSHIP    family
1                       0
2       NULL            0
3       Parent          1
4       Child           1

ITEM    RELATIONSHIP    family
1                       0
2       NULL            0
3       Parent          1
4       Child           1

Is this because all scalar comparison of NULL values are always false and (false AND false) is always false?

CodePudding user response：

If I am correct, the result is different only for the second row. For the first query, the condition WHEN (RELATIONSHIP != 'Parent' AND RELATIONSHIP != 'Child') is false for NULL and for the second query, the condition WHEN (RELATIONSHIP = 'Parent' OR RELATIONSHIP = 'Child') is also false for NULL.

You have to handle NULL explicitly in sql server using IS NULL operator. I assume you have the confusion in your first query result. So in your first query, the condition should be

WHEN (RELATIONSHIP != 'Parent' AND RELATIONSHIP != 'Child') OR RELATIONSHIP IS NULL

SQL here

CodePudding user response：

Comparison with NULL with any value (including NULL) results in UNKONWN which in binary logic will be translated to false.

Therefore x = NULL is UNKONWN (false) and x != NULL is UNKONWN (false) too. (Similarly x < NULL, x <= NULL, x > NULL, x >= NULL, etc will also result in UNKONWN).

NULL = NULL is also UNKONWN (false).

The only exception is testing against NULL via IS NULL.

ITEM    RELATIONSHIP    family    RELATIONSHIP = 'Parent'   RELATIONSHIP != 'Parent'
1                       0         false                     true
2       NULL            1         UNKONWN (false)           UNKONWN (false)
3       Parent          1         true                      false
4       Child           1         false                     true