DISTINCT NULL return single NULL in SQL Server

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)

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.

Tags:

Sql Server