Handling NULL in T-SQL

Table of Contents

• Foreword
• General Perceptions of NULL
• What is NULL?
• Functions and Operators for Handling NULL Values
  1. ISNULL
  2. COALESCE
  3. NULLIF
  4. IS NULL and IS NOT NULL
• NULL in Relational Operators
• NULL in Conditional Operators
  1. IN and NOT IN
  2. BETWEEN and NOT BETWEEN
• NULL in Order By(Sorting)
• NULL in Group By
• NULL in Aggregated Methods

Foreword

Displaying error message is always better than generating wrong output. When you deal with NULL, there might be a chance of getting output even though the code has some logical errors. It plays a vital role to maintain the quality and accuracy on database output.

In this article, I would like to describe different aspects, general perceptions and best practices about NULL in T-SQL.

Your comments are highly appreciated. :).

General Perceptions of NULL

1. It is blank.
2. It is empty.
3. It is zero.
4. It is nothing.
5. It is missing value.
6. It is the lowest value.
7. It is ignorable value.
8. It is optional value.
9. It is invalid.
10. It is void.

What is NULL?

NULL means 'NO RESULT' or 'UNKNOWN' which is not equal to itself.

     NULL <> NULL  --NULL does not equals NULL.
     if(NULL=NULL) -- It returns nothing(no error and no result).
     NULL + Anything=NULL -- If you add anything to add, it always return NULL.

CODD's Rule 3: Systematic treatment of null values:

The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

Microsoft

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Wiki

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Functions and Operators for Handling NULL Values

The COALESCE and ISNULL T-SQL functions are used to return the first non-null expression among the input arguments. Both are used to handle the NULL value in T-SQL. ISNULL takes two arguments and COALESCE takes more than two arguments as required.

 
     declare @x int=null;
     Select ISNULL(@x,'0') AS ISNULL_OUTPUT
     *************************************************** 
     Output : 
       ISNULL_OUTPUT
           0
     --@x is null that is replaced with 0
     *************************************************** 
 

     declare @x int=null;
	 declare @y int=null;
	 declare @z int=20;
     COALESCE(@x,@y,@z,'0') as COALESE_OUTPUT
     --*************************************************** 
     Output : 
       COALESE_OUTPUT
           20
     --First and second argument(@x and @y) are null so that COALESE return first 
     --non-NULL argument as 20(a value of @z)
     --***************************************************  

NULLIF

NULLIF takes two arguments and returns NULL if the arguments are NULL otherwise return first argument.

declare @x int=0;
select NULLIF(@x,0) as Result -- return NULL if @x is 0
--**************************************
Output
Result
NULL
--**************************************

IS NULL and IS NOT NULL

It is not possible to test for NULL values with relational operators like =, <, or <>. In order to check whether a value is NULL or not, we need to use IS NULL or IS NOT NULL operator.

 declare @value int=null;
 if @value is NULL
 begin
   Select 'Value is NULL' AS Result
 end

 set @value=1
 if @value is NOT NULL
 begin
   Select 'Value is not NULL' As Result
 end

NULL in Relational Operators

• NULL is not equal to any value
• NULL is not greater, less or different from value
• NULL is not equal to NULL itself.
• NULL is not greater than, less than or different from NULL

NULL in Conditional Operators

IN and NOT IN

• IN does not return a match on a value that is NULL
• NOT IN returns false if at least one of the values is NULL

declare @Temp table(
	  Col1 int,
	  Col2 varchar(20)
  	)
insert into @Temp values(1,'Test 1')
insert into @Temp values(2,'Test 2')
insert into @Temp values(3,'Test 3')
insert into @Temp values(4,'Test 4')
insert into @Temp values(5,'Test 5')
insert into @Temp values(null,'Test 6')
insert into @Temp values(7,'Test 7')
insert into @Temp values(null,'Test 8')
insert into @Temp values(null,'Test 9')
insert into @Temp values(10,'Test 10')

select * from @Temp where Col1 in(1,3,6)
--It returns 3 rows

select * from @Temp where Col1 in(1,3,6,null)
--It does not include null so it returns 3 rows 

select * from @Temp where Col1 not in(1,3,6,null)
-- it does not return any result 

BETWEEN and NOT BETWEEN

• BETWEEN and NOT BETWEEN return false if one of the limits value is NULL

	declare @Temp table(
	  Col1 int,
	  Col2 varchar(20)
  	)
insert into @Temp values(1,'Test 1')
insert into @Temp values(2,'Test 2')
insert into @Temp values(3,'Test 3')
insert into @Temp values(4,'Test 4')
insert into @Temp values(5,'Test 5')
insert into @Temp values(null,'Test 6')
insert into @Temp values(7,'Test 7')
insert into @Temp values(null,'Test 8')
insert into @Temp values(null,'Test 9')
insert into @Temp values(10,'Test 10')

select * from @Temp where Col1 between 1 and 7
--Returns 6 rows
select * from @Temp where Col1 between null and 7
--No result
select * from @Temp where Col1 between 1 and null
--No result
select * from @Temp where Col1 not between 1 and 7
--Returns 1 row
select * from @Temp where Col1 not between null and 7
--Returns 1 row
select * from @Temp where Col1 not between 1 and null
--No result

NULL in Order By(Sorting)

NULL is the smallest value in the sorting order.

NULL in Group By

NULLs are considered to be equal when the group by is executed. If a column in the GROUP BY clause contains rows with NULL, then these will be grouped into one group.

NULL in Aggregated Methods

The aggregate functions – COUNT, SUM, AVG, MAX, MIN and LIST – don't handle NULL. There is one exception to this rule: COUNT(*) returns the count of all rows, even rows whose fields are all NULL. But COUNT(FieldName) behaves like the other aggregate functions in that it only counts rows where the specified field is not NULL.

History

• 2014-10-04: Initial version