Programmer's Rules

SQL Server

How SQL Server processes NULL

Concise description of using unknown and missing values in SQL Server

SQL Serverjob interview70-761

When comparing two existing values in SQL Server it is possible to get two results: TRUE or FALSE. For example, the following script:

prints 'FALSE'. In every case, when setting any values to variables @a and @b the result will be true or false and we never achieve the ELSE case.

However, in cases when the result of comparing values is unknown, then apart from true or false we can get NULL. The above is shown in the following scripts:

or:

The example results of comparisons with NULL are shown below:

In query filters such as WHERE, ON and HAVING when comparing with NULL, getting the unknown values is equivalent to FALSE. It means that values comparing with NULL in test [column_value] = NULL will not be shown as a result.

ORDER BY [column_name] ASC shows the sorted NULLs together as first fields. ORDER BY [column_name] DESC puts the sorted NULLs at the END.

DISTINCT treats NULLs as equal so it is possible to achieve unique values.

It is possible to use NULLs in CHECK constraints.
Tutorial statistics