Click here to Skip to main content
14,301,484 members

Handling NULL in T-SQL

Rate this:
4.80 (18 votes)
Please Sign up or sign in to vote.
4.80 (18 votes)
23 Jan 2017CPOL
Handling NULL in T-SQL

Table of Contents

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.

SNISNULL
1.SQL Server standard.
2.Returns the first argument if it is not NULL.
3.Returns the second argument if it is NULL.
4.Syntax: ISNULL(argument1,argument2)
argument1: Expression
argument2: Replacement value
5.Example:
 declare @x int=null;
 Select ISNULL(@x,'0') AS ISNULL_OUTPUT
 ***************************************************
 Output : 
   ISNULL_OUTPUT
       0
 --@x is null that is replaced with 0
 ***************************************************
SNCOALESCE
1.ANSI standard.
2.Returns the first non-NULL argument
3.Returns NULL if all arguments are NULL
4.Syntax: COALESCE ( arguments [1.......n ] )
n: Arguments
5.Example:
 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

Image 1

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.

Image 2

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.

Image 3

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.

Image 4

History

  • 2014-10-04: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bhim B Thapa
Software Developer APTA
United States United States
No Biography provided

Comments and Discussions

 
QuestionNULLIF returns NULL if the two arguments are the same. Pin
User987437-May-18 8:47
professionalUser987437-May-18 8:47 
QuestionMy vote of 5 Pin
Member 1109503412-Feb-17 23:21
memberMember 1109503412-Feb-17 23:21 
QuestionNice article, just want to confirm on one statement Pin
SandeepKParashar24-Jan-17 19:45
memberSandeepKParashar24-Jan-17 19:45 
AnswerRe: Nice article, just want to confirm on one statement Pin
Bhim B Thapa25-Jan-17 11:57
professionalBhim B Thapa25-Jan-17 11:57 
QuestionNice summary. Pin
Member 1256394227-Jul-16 11:18
memberMember 1256394227-Jul-16 11:18 
AnswerRe: Nice summary. Pin
Bhim B Thapa27-Jul-16 16:57
professionalBhim B Thapa27-Jul-16 16:57 
QuestionMy vote of 5 Pin
RUs1238-Oct-14 0:02
memberRUs1238-Oct-14 0:02 
AnswerRe: My vote of 5 Pin
Bhim B Thapa8-Oct-14 1:36
professionalBhim B Thapa8-Oct-14 1:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 4 Oct 2014

Stats

53.9K views
36 bookmarked