MS SQL Server Unexpected Behavior

Question

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?

Surprisingly ANSI_NULLS being ON or OFF doesn't make any difference in the results. — MRodriguez, Sep 16 '22 at 17:10
It's because they both fall through to the `else` case. But those have different outputs. — shawnt00, Sep 16 '22 at 17:17
Because THEN branch of CASE is only returned when the WHEN expression is TRUE. NULL comparison is never TRUE, it's NULL. So whatever the expesion including NULL value is, ELSE branch is returned — Serg, Sep 16 '22 at 17:18
Actually it's ternary logic. You're dealing with logical values of TRUE, FALSE, and UNKNOWN. That's why they both fall through. — shawnt00, Sep 16 '22 at 17:28

Md. Suman Kabir · Answer 1 · 2022-09-16T17:24:46.957

0

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

edited Sep 16 '22 at 17:24

answered Sep 16 '22 at 17:18

Md. Suman Kabir

5,243
5
25
43

I had included the "IS NULL" check version in the third select above. It's just caught me off guard because we usually think of checking for null values on the RHS of an expression and not the left. Also, it is a bit counter-intuitive from a human perspective (not programming or mathematical) that unknown != known is false and unknown = known is false. – MRodriguez Sep 16 '22 at 17:59

Pred · Answer 2 · 2022-09-16T17:30:05.933

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

MS SQL Server Unexpected Behavior

2 Answers2