Introduction
to NULL :
NULL :A NULL value is a special value that represents
an unknown value. SQL Server supports NULL values with the following features:
- All
data types used for table columns support NULL values. In another word,
NULL values can be stored in database tables.
- Individual
table columns may be defined to not allow NULL values. In this case, you
can not assign NULL values to those columns.
- "NULL"
is a keyword that represent a NULL value in expressions.
- NULL
values can be used directly in SET (assignment) statements.
- If
NULL values are involved in an arithmetic operation, the result will be a
numeric NULL.
- If
NULL values are involved in a string operation, the result will be a
string NULL.
- If
NULL values are involved in a datetime operation, the result will be a
datetime NULL.
- If
NULL values are involved in a bitwise operation, the result will be a
binary NULL.
- If
NULL values are involved in a comparison operation, the result will be a
Boolean NULL.
- If
NULL values are involved in a Boolean operation, the result could be TRUE,
FALSE, or NULL.
- To
test NULL values, you need to use two special operators, IS NULL and IS
NOT NULL.
- Special
functions are available to handle NULL values, like ISNULL(), and NULLIF()
How
To Assign NULL Values to Variables or Columns?
The rule for assigning NULL values to variables
or table columns is simple: Use keyword "NULL" directly as normal
values. Specificly,
- "NULL"
can be used in SET statements to assign NULL values to variables.
- "NULL"
can be used in SET clauses in UPDATE statements.
- "NULL"
can be used in value lists in INSERT statements.
- "NULL"
can be used in parameter lists when calling stored procedures or
functions.
Assiging NULL values to variables :
DECLARE
@birth_date DATETIME;
SET
@birth_date = NULL;
SELECT
@birth_date;
GO
--------------
NULL
Assiging NULL values to columns:
UPDATE datson SET notes = NULL;
GO
Assiging NULL values to parameters:
EXEC sp_help NULL;
GO
What
Happens If NULL Values Are Involved in Arithmetic Operations?
If NULL values are involved in arithmetic
operations, the result will be numeric NULL values.
SELECT 7+NULL;
GO
-----------
NULL
SELECT 10.02*NULL;
GO
-----------
NULL
SELECT 4.988E+10/NULL;
GO
-----------
NULL
What
Happens If NULL Values Are Involved in String Operations?
If NULL values are involved in string
operations, the result will be string NULL values.
SELECT 'sunil'+NULL;
GO
----------
NULL
SELECT LEN(NULL);
GO
----------
NULL
SELECT REVERSE(NULL);
GO
----------
NULL
What
Happens If NULL Values Are Involved in Datetime Operations?
If NULL values are involved in datetime
operations, the result will be datetime NULL values.
SELECT GETDATE()+NULL;
GO
-----------
NULL
SELECT DATEDIFF(DAY, GETDATE(), NULL);
GO
-----------
NULL
What
Happens If NULL Values Are Involved in Bitwise Operations?
If NULL values are involved in bitwise
operations, the result will be binary NULL values.
SELECT 1 | NULL;
GO
-----------
NULL
SELECT 707 & NULL;
GO
-----------
NULL
SELECT ~NULL;
GO
-----------
NULL
What
Happens If NULL Values Are Involved in Comparison Operations?
If NULL values are involved in comparison
operations, the result will be Boolean NULL values. This behavior is very
interesting because you would expect a comparison operation returns only one of
the two values: TRUE and FALSE. But SQL Server may return you a third value:
NULL.
IF 1>NULL PRINT '1>NULL is returning TRUE'
ELSE PRINT '1>NULL is not
returning TRUE'
GO
1>NULL
is not
returning TRUE
IF NOT 1>NULL PRINT '1>NULL is returning
FALSE'
ELSE PRINT '1>NULL is not
returning FALSE'
GO
1>NULL
is not
returning FALSE
Another test proves that "'FYI'=NULL"
is not returning TRUE or FALSE. It is returning a third value, NULL
IF 'FYI'=NULL PRINT '''FYI''=NULL returns
TRUE'
ELSE PRINT '''FYI''=NULL does not
return TRUE'
GO
'FYI'=NULL does not return TRUE
IF NOT 'FYI'=NULL PRINT '''FYI''=NULL returns FALSE'
ELSE PRINT '''FYI''=NULL does not
return FALSE'
GO
'FYI'=NULL does not return FALSE
What
Happens If NULL Values Are Involved in Boolean Operations?
If NULL values are involved in Boolean
operations, the result will vary depending on the operator type. For AND
operator, FALSE takes precedence over NULL. The result can be summarized in a
table below:
AND TRUE
FALSE NULL
TRUE true
false null
FALSE false
false false
NULL null false
null
For OR operator, TRUE takes precedence over
NULL. The result can be summarized in a table below:
OR TRUE
FALSE NULL
TRUE true
true true
FALSE true
false null
NULL true
null
null
The tutorial script below shows you that NULL
AND FALSE returns FALSE:
IF 0=NULL AND 0=1 PRINT 'NULL AND FALSE returns TRUE'
ELSE PRINT 'NULL AND FALSE does
not returns TRUE'
GO
NULL AND FALSE does not returns TRUE
IF NOT (0=NULL AND 0=1)
PRINT 'NULL AND FALSE returns FALSE'
ELSE PRINT 'NULL AND FALSE does
not returns FALSE'
GO
NULL AND FALSE returns
FALSE
How
To Replace NULL Values in Expressions using ISNULL()?
NULL values presented in expressions will cause
the final results to be NULL. Sometimes, you want NULL values to be replaced
with some default values, like 0, '', or 'NULL', so that expressions can be
evaluated properly.
SQL Server offers a built-in function called
ISNULL() to help you replacing NULL values in expressions:
ISNULL(expression, replacement)
-- Returns
"expression", if it is not NULL
-- Returns
"replacement", if "expression" is NULL
The tutorial example below shows you how to
replace possible NULL values in @middle_initial:
CREATE PROCEDURE welcome
@first_name VARCHAR(20),
@middle_initial VARCHAR(1),
@last_name VARCHAR(20)
AS
PRINT 'Hello '+@first_name
+ ' '+@middle_initial
+ ' '+@last_name;
PRINT 'Hello '+@first_name
+ ' '+ISNULL(@middle_initial,'')
+ ' '+@last_name;
GO
EXEC welcome 'John', 'W', 'King';
GO
Hello John W King
Hello John W King
EXEC welcome 'John', NULL, 'King';
GO
Hello John King
The first PRINT statement in the second test
returns a blank line because of NULL value
How
To Replace Given Values with NULL using NULLIF()?
Sometime you want to hide certain values by
replacing them with NULL values. SQL Server offers you a nice function called
NULLIF() to do this:
NULLIF(expression, value)
-- Returns NULL
if "expression" equals to value"
-- Returns
"expression", otherwise
NULLIF() can be viewed as the reverse function
of ISNULL(). The tutorial script below shows you a good example of using
NULLIF():
SELECT id, counts FROM
fyi_links;
GO
id counts
-----------
-----------
101 NULL
102 8
1101 NULL
202 NULL
2101 NULL
2102 NULL
301 NULL
302 NULL
-- converting
NULL to 0
UPDATE fyi_links SET counts=ISNULL(counts,0);
GO
SELECT id, counts FROM
fyi_links;
GO
id counts
-----------
-----------
101 0
102 8
1101 0
202 0
2101 0
2102 0
301 0
302 0
-- converting 0
to NULL
UPDATE fyi_links SET counts=NULLIF(counts,0);
GO
SELECT id, counts FROM
fyi_links;
GO
id counts
-----------
-----------
101 NULL
102 8
1101 NULL
202 NULL
2101 NULL
2102 NULL
301 NULL
302 NULL
