Instances that treat NULLs as equal values

NULLs in databases are considered as unknown values and can never be compared with another. This behavior (or the known fact) is common to Microsoft SQL Server too. That is the reason for failing queries written with an equality for NULLs;

USE AdventureWorks2012

GO

-- This returns no records

SELECT *

FROM Production.Product

WHERE Color = NULL

However, there are instances in SQL Server that treat NULLs as equal values. One instance is queries written with ORDER BY clause using nullable columns. ORDER BY treats NULLs as equal values and sorts them together. That is why we see NULLs at the top of the ordered results.

-- Products that have NULL for

-- Color will be appeared first

SELECT Name, Color

FROM Production.Product

ORDER BY Color

The other instance is, queries written with DISTINCT. DISTINCT treats NULLs as equal too. Because of that, you get only one record for NULLs if DISTINCT is based on a nullable column.

-- There will be one record

-- for all NULLs

SELECT DISTINCT Color

FROM Production.Product

ORDER BY Color