NULL in T-SQL

In T-SQL NULL represents the missing value. This means if the value for a column is not provided while row insertion then the NULL marker will be used in that column(if the column is nullable). Note that NULL is not a value.

Various components of T-SQL language treat NULL in a different way. NULL can be used as a constraint and also in predicates to filter rows and groups.

First, we’ll discuss the role of NULLs in the querying part of the T-SQL language.

T-SQL use three value predicate logic. This means the predicate evaluates to TRUE, FALSE or UNKNOWN(when comparing a value to NULL).

In T-SQL TRUE and FALSE behaves as expected. For example, for predicate age > 0 if the age is greater than 0 then it returns TRUE and the row will be returned if the predicate is in query filter(WHERE or HAVING clause) else row will be rejected.

T-SQL treats the UNKNOWN in a slightly different and unexpected way. For query filters T-SQL is based on “accept TRUE”. This means the rows for which the predicate evaluates to FALSE or UNKNOWN will be rejected. If you want to include the rows where predicate evaluates to UNKNOWN then we can use IS NULL in our predicate. For example, for the predicate age > 0 AND age IS NULL the rows for which age is greater than 0 and age is null will be included in our result set. We can not use AGE = NULL or AGE != NULL rather use IS NULL or IS NOT NULL because NULL is a marker, not a value.

In the context of negation, the TRUE and FALSE behaves as expected i.e. NOT TRUE is FALSE and NOT FALSE is TRUE. But NOT UNKNOWN results in UNKNOWN again.

In the case of grouping and sorting NULLs got again different treatment i.e. NULLs are considered equal in case of grouping and sorting. GROUP BY clause groups the NULLs in one group as other present values. ORDER BY clause orders the NULLs together. T-SQL sorts NULLs before other present values.

In the case of UNIQUE constriant, the NULLs are considered equal again. this means if a column in table got UNIQUE constraint and is nullable then only one NULL will be allowed in that column.

NULL Constraint

We have been talking about NULL in queries but we can use NULL as a constraint as well to decide whether the NULLs are allowed for a given column or not in case of insert/update operations. We can achieve this while creating the table definition. For example, let say we want a table named Persons with columns FirstName, LastName and DateOfBirth. FirstName and LastName are not allowed to have NULLs and DateOfBirth can have NULLs.

CREATE TABLE dbo.Persons
(
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DateOfBirth DATETIME NULL,
);

Note that NULL and NOT NULL keywords at end of each column definition.