How to handle null exceptions, what expressions are used to handle null values?

Problem: How to handle null exceptions, what expressions are used to handle null values?

Solution: When a table is designed to have allow nulls on the column, we can expect nulls to be populated in that columns rows. But when comparing the values nulls will not be picked as they

NULLIF (Transact-SQL)    – Checks for two expressions and returns a null if both expressions match

COALESCE (Transact-SQL) – Returns the first non-null value.

CASE (Transact-SQL)        -Can be used with a combination of IS NULL or IS NOT NULL operator.

Depending on the use cases each of these can play vital role in handling values.

NULLIF Syntax:

NULLIF ( expression , expression )

COALESCE Syntax:

COALESCE ( expression [ ,...n ] ) 

CASE Syntax:

CASE input_expression 

     WHEN when_expression THEN result_expression [ ...n ] 

     [ ELSE else_result_expression ] 

END 

Searched CASE expression:

CASE

     WHEN Boolean_expression THEN result_expression [ ...n ] 

     [ ELSE else_result_expression ] 

END

Example:

In this example, I would like to show we can use null handling expressions.

-- Create Table with nullvalues column

IF OBJECT_ID('nullvalues', 'U') IS NOT NULL

  DROP TABLE [dbo].[nullvalues]

GO

CREATE TABLE [dbo].[nullvalues]

  (

  [OrderID] INT PRIMARY KEY NOT NULL,

  [OrderName] VARCHAR(50)  NULL,

  [OrderedQuantity] INT NOT NULL,

  [unitPrice] DECIMAL(16,2) NOT NULL,

  [OrderedPrice] AS [OrderedQuantity]*[unitPrice] PERSISTED

)

GO

--Insert sample data

INSERT INTO nullvalues (OrderID, OrderName, OrderedQuantity,unitPrice)

SELECT 1, 'Fans',2, 500.00 UNION ALL

SELECT 2,NULL, 1,100.00 UNION ALL

SELECT 3, NULL, 10,1000.00 UNION ALL

SELECT 4, 'Chairs', 10,120.00

GO

-- using NULL IF

SELECT NULLIF(Ordername,'NULL') NULLIF_RESULTS FROM dbo.nullvalues

GO

OUTPUT:

NULLIF_RESULTS

Fans

NULL

NULL

Chairs

-- using COALESCE

SELECT COALESCE(Ordername,'unkown order') COALESCE_RESULTS FROM dbo.nullvalues

GO

OUTPUT:

COALESCE_RESULTS

Fans

unkown order

unkown order

Chairs

-- using CASE WITH IS NULL

SELECT CASE WHEN OrderName IS NOT NULL THEN OrderName

WHEN Ordername IS NULL THEN 'unkown order' END as CASE_EXPRESSION FROM dbo.nullvalues

GO

OUTPUT:

CASE_EXPRESSION

Fans

unkown order

unkown order

Chairs