SQL Server Working with NULL | Null Functions | Expressions | Comparisons | Examples

SQL Server Working with NULL | Null Functions | Expressions | Comparisons | Examples 

What is a NULL value in SQL? 

In SQL, a NULL value helps to identify that particular attribute/column data is not available in the table. As per BOL, "Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database".

It is important that Null doesn't indicate any value such as Zero (0) or blank. NULL as meaning "unknown".

For example, In the employee data, the bonus field applies only to employees who are eligible for the bonus, and others it will be empty (NULL). 

Null values in the database tables

By default, all the columns in the table allow NULL value unless we specify NOT NULL constraint.  

During data insertion, if any optional field is missing then SQL Server places the NULL value in that field to denote an unknown or missing value. Note, The NULL value can't be applied if there is a default value set.

USE tempdb;
CREATE TABLE product(PRODUCT_ID INT NOT NULL, PRODUCT_NAME VARCHAR(45) NOT NULL, PCODE VARCHAR(45), PSTATUS INT DEFAULT 1)
INSERT INTO product VALUES (1,'Ear-Phones','CVR',2);
INSERT INTO product(PRODUCT_ID,PRODUCT_NAME) VALUES (2,'Mobile Chargers');
SELECT * FROM product;
*******************RESULT***********************
PRODUCT_ID  PRODUCT_NAME           PCODE          PSTATUS
----------- ---------------------- -------------- ---------- 
1           Ear-Phones             CVR            2
2           Mobile Chargers        NULL           1

(2 rows affected)

Null values in comparison

You can use IS NULL or IS NOT NULL comparisons to check for NULL values. For example 

SELECT * FROM product where PCODE IS NULL
*******************RESULT***********************
PRODUCT_ID  PRODUCT_NAME           PCODE          PSTATUS
----------- ---------------------- -------------- ---------- 
2           Mobile Chargers        NULL           1

SELECT * FROM product where PCODE IS NOT NULL
*******************RESULT***********************
PRODUCT_ID  PRODUCT_NAME           PCODE          PSTATUS
----------- ---------------------- -------------- ---------- 
1           Ear-Phones             CVR            2

Null values in a boolean expression

The default result of most operands (such as AND, OR, +, and -) is NULL when either operand is NULL. The AND operator returns TRUE only if all conditions are also TRUE. The OR operator return TRUE when one or more conditions are also TRUE.

	TRUE	FALSE	NULL
AND
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL
OR
TRUE	TRUE	TRUE	TRUE
FALSE	FALSE	FALSE	NULL
NULL	TRUE	NULL	NULL

Arithmetic Expressions

10 + NULL = NULL
NULL + (5*4) = NULL
10 -(5*4)/10 - NULL = NULL

String Expressions

'mahesh'+' '+ NULL = NULL

CONCAT_NULL_YIELDS_NULL

Controls whether concatenation results are treated as null or empty string values.
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'Mahesh' + NULL gives NULL.
When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'Mahesh' + NULL gives Mahesh.

set CONCAT_NULL_YIELDS_NULL off
select 'mahesh'+'_'+NULL 
set CONCAT_NULL_YIELDS_NULL on
 -- Result: mahesh_ 

SQL Server NULL functions

SQL Server includes below functions to handle nulls when used within expressions.

• ISNULL()
• COALESCE()
• NULLIF()

ISNULL()

This function validates whether a source expression is not equal to NULL, then the ISNULL function will return source value. However, if the source expression is NULL, then the replacement value will be returned. 

Syntax: ISNULL(source_expression, replacement_value)

SELECT ISNULL('SATHISH',NULL) AS RESULT1
select ISNULL(NULL,'MAHESH') AS RESULT2
DECLARE @NAME varchar(5) = NULL
SELECT ISNULL(@NAME,1) AS RESULT3
DECLARE @ID INT = NULL
SELECT ISNULL(@ID,'A') AS RESULT4
RESULT1
-------
SATHISH

RESULT2
-------
MAHESH

RESULT3
-------
1

RESULT4
-----------
Msg 245, Level 16, State 1, Line 33
Conversion failed when converting the varchar value 'A' to data type int.

As you see the last select statement is failed, this will happen if the source type is not matched with the replacement value type. Here ISNULL function tries to convert the replacement value with the source type.

COALESCE()
This function accepts a list of expressions and returns the first non-null value. COALESCE expects at least one of the expression that is not null, If all the expression contains NULL then it returns an error.

Syntax: COALESCE(expression1, expression2, ....)

SELECT COALESCE(NULL,NULL,NULL) AS RESULT1
SELECT COALESCE(NULL,NULL,'MAHESH') AS RESULT2
DECLARE @NAME varchar(5) = NULL
SELECT COALESCE(NULL,@NAME,1) AS RESULT3
DECLARE @ID INT = NULL
SELECT COALESCE(NULL,@ID,'A') AS RESULT4

RESULT1
-------
Msg 4127, Level 16, State 1, Line 37
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

RESULT2
-------
MAHESH

RESULT3
-----------
1

RESULT4
-----------
Msg 245, Level 16, State 1, Line 47
Conversion failed when converting the varchar value 'A' to data type int.

NULLIF()
This function accepts two expressions. If two are equal, then it returns a null; other it returns the first expression. NULLIF function can't allow null in the first expression.

Syntax: NULLIF(expression1, expression2)

select NULLIF('MAHESH','MAHESH') AS RESULT1
select NULLIF('MAHESH','SATHISH') AS RESULT2
select NULLIF(null,'A') AS RESULT3
SELECT NULLIF('MAHES',1) AS RESULT4

RESULT1
-------
NULL

RESULT2
-------
MAHESH

RESULT3
-------
Msg 4151, Level 16, State 1, Line 40
The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

RESULT4
-------
Msg 245, Level 16, State 1, Line 41
Conversion failed when converting the varchar value 'MAHES' to data type int.

Thanks for your time. If you have any suggestions or queries, please comment below.