As developers, we all probably know what a NULL is in SQL Server. NULL is not a specific value but it stands for UNKNOWN value. In other words, NULL represents the ‘absence of data’.
The behavior and treatment of NULL in SQL server is very interesting in different context and part of T-SQL language. Let’s have a quick look on how it is treated in different ways by SQL Server.
When you use NULL (UNKNOWN value) in Arithmetic operations or in String Concatenation operations, the result is always UNKNOWN because the value which is being operated is not available or UNKNOWN. So, all the following statements yield the result of NULL:
SELECT 5 + NULL
SELECT 10 / NULL
SELECT 'Gateway' + ' Technolabs' + NULL
So beware when you are trying to do something similar as shown below:
DECLARE @TOP INT
DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP ' + CAST(@TOP AS VARCHAR(10)) + '* FROM HumanResources.Employee'
EXECUTE SP_EXECUTESQL @SQL
The above query will never execute. Guess why? Because you forgot to initialize @TOP variable and due to that it will contain NULL. So when you try to concatenate the value of @TOP with the value of @SQL, the resulting query is UNKNOWN.
Unlike other programming languages, SQL Server follows the rules of “Three-Valued Logic” which means that the result of a condition in T-SQL can have any of three possible values viz. TRUE, FALSE and UNKNOWN. If NULL is one of the operands in condition composed of comparison operators like =,>,<,>=,<=,<>, then the result of the expression is NULL or UNKNOWN. For example, the result of all of the following conditions is UNKNOWN:
… WHERE 100 = NULL
… WHERE NULL > 5
… WHERE NULL < 10
Even following statement yields NULL as per SQL-92 Standards:
… WHERE NULL = NULL
Let’s create a simple table and insert some records in it.
CREATE TABLE tblPerson
(
PersonID INT
,PersonName VARCHAR(50)
,Age TINYINT
)
GO
INSERT INTO tblPerson
SELECT 1,'Person-1',25
UNION
SELECT 2,'Person-2',NULL
UNION
SELECT 3,'Person-3',35
We have a table which contains three rows. The second row has NULL in Age for Person-2. Now, execute the following query:
SET ANSI_NULLS OFF
SELECT * FROM tblPerson WHERE Age = NULL
The above query will return 1 row for Person-2. Notice the use of SET ANSI_NULLS OFF which is not recommended way because it does not conform to SQL-92 standards. Microsoft encourages the usage of SET ANSI_NULLS ON which is considered best practice and which is also the default behavior of SQL Server. Now, consider following query:
SET ANSI_NULLS ON
SELECT * FROM tblPerson WHERE Age = NULL
The result of the condition in WHERE clause in above query will be UNKNOWN. It is neither TRUE nor FALSE. But, in this case SQL server treats NULL as if “NOT EQUAL TO” and evaluates the result of the condition to FALSE and will not return any rows. Note the use of SET ANSI_NULLS ON, which is SQL Server default.
If you want to fetch rows where Age is NULL, you should use ‘IS’ operator with ANSI_NULLS set to ON instead of using ‘=’ operator with ANSI_NULLS set to OFF. So, the preferred query should be as follows:
SET ANSI_NULLS ON
SELECT * FROM tblPerson WHERE Age IS NULL
As you can observe from above examples that using comparison operators (=,<,>,>=,<=,<>) in condition with NULL as one of its operands appearing in WHERE clause yields UNKNOWN result when you don’t specify ANSI_NULLS option or if it is set to ON.
But this is not the case when NULL appears in condition for a CHECK constraint. To see this, let’s add a CHECK constraint to our table so that age must be greater than zero when a record is inserted or updated.
ALTER TABLE tblPerson
ADD CONSTRAINT CK_tblPerson_Age CHECK (Age > 0)
Now, add new row with NULL in Age as shown below:
INSERT INTO tblPerson VALUES (4,'Person-4',NULL)
The age for newly inserted row is NULL which is not greater than zero because it is UNKNOWN. But, SQL Server treats NULL differently in this case and evaluates the CHECK constraint condition as if it is TRUE and inserts new row to the table.
You saw that the condition WHERE NULL=NULL yields NULL because both the values are UNKNOWN and due to this comparison is not possible. Thus, the result will always be UNKNOWN. But again, this is not always the case. To prove my point, let’s add a UNIQUE constraint on PersonID column.
ALTER TABLE tblPerson
ADD CONSTRAINT UK_tblPerson_Age UNIQUE (PersonID)
Now insert a new row where PersonID is NULL as shown below:
INSERT INTO tblPerson VALUES (NULL,’Person-5′,30)
Because PersonID column allows NULL values, SQL Server will insert this new row. Now, try inserting one more row where PersonID is NULL:
INSERT INTO tblPerson VALUES (NULL,'Person-6',35)
As you may probably have guessed, because of UNIQUE constraint on PersonID SQL Server will not allow inserting another row with NULL for PersonID because tblPerson already contains one row with NULL in PersonID column. In this case, SQL server treats NULL even differently and considers all NULLs as if they are equal and does not allow a second NULL to be inserted. Even when SQL Server groups or orders rows by a column, it considers all the NULLs in one group as if they are all equal.
I hope this discussion will help many of you in understanding the behavior of NULL in SQL Server. Thank you for taking time to read this article. Readers’ valuable comments are always welcome!
) to find out the following two possibilities out of my one hour of investment:
