Saturday, February 1, 2014

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

image

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

image

No comments:

Post a Comment