Table of Contents
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. :).
- It is blank.
- It is empty.
- It is zero.
- It is nothing.
- It is missing value.
- It is the lowest value.
- It is ignorable value.
- It is optional value.
- It is invalid.
- It is void.
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.
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.
SN | ISNULL |
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
***************************************************
|
SN | COALESCE |
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
|
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
Output
Result
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
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
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)
select * from @Temp where Col1 in(1,3,6,null)
select * from @Temp where Col1 not in(1,3,6,null)
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
select * from @Temp where Col1 between null and 7
select * from @Temp where Col1 between 1 and null
select * from @Temp where Col1 not between 1 and 7
select * from @Temp where Col1 not between null and 7
select * from @Temp where Col1 not between 1 and null
NULL
is the smallest value in the sorting order.
NULL
s 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.
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