17

In Sql Server, NULL IS NOT EQUAL TO NULL.(Why does NULL = NULL evaluate to false in SQL server) Then why the following code returns single NULL.

CREATE TABLE #TEMP1
    (ID INT)
INSERT INTO #TEMP1
SELECT NULL
UNION ALL
SELECT NULL
SELECT DISTINCT ID FROM #TEMP1
DROP TABLE #TEMP1

ID
------
NULL

I expected 

ID
------
NULL
NULL
Improve this question
1

2 Answers 2

Reset to default
35

The handling of NULLs for DISTINCT comparison is clearly called out in the documentation. Excerpt:

DISTINCT

Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

Improve this answer
1
  • In the SQL standard DISTINCT in UNION follows your quote. However in other places rows that are the same but have NULL are distinct. For UNION DISTINCT MS Server follows the standard but in other places it doesn't. Which I mention for anyone using a different DBMS.
    – philipxy
    Commented May 17, 2019 at 21:30
1

Not only is it called out in the documentation, it’s a different kind of equality test. Does unknown equal unknown? Who knows, maybe yes, maybe no. But that is exactly why Distinct should return only 1. If it returned 2 different values, that would be saying that the unknowns were different. There exists at least one unknown value so, it should be in the list, whether it is different from any of the other unknown values is, well, unknown.

Null values compare as equal for Intersect as well.

Select 1
Where exists (select null intersect select null)
Improve this answer
1
  • NULLs in SQL are ad hoc & you can't reason that they act somewhere based on how they act elsewhere or on some notion of "unknown" that inspired them. There's no "should". SQL standard UNION DISTINCT is unlike other use of distinct & in MS Server other uses of distinct don't follow the standard.
    – philipxy
    Commented May 17, 2019 at 21:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.