Display Non-Null Values Even If A Column Contains Null

Our tables can contain NULL values if the columns allows them. However while displaying the data to the customers, we often have to display non-null values even if a column contains null. Let us see how to do that :

-- CREATE SAMPLE TABLE

CREATE TABLE #Customers (id integer, cname varchar(20), pincode int)

-- SAMPLE DATA

INSERT INTO #Customers VALUES (1, 'Jack',45454 )
INSERT INTO #Customers VALUES (2, 'Jill', 43453)
INSERT INTO #Customers VALUES (3, 'Tom', 43453)
INSERT INTO #Customers VALUES (4, 'Kathy', null)
INSERT INTO #Customers VALUES (5, 'David', 65443)
INSERT INTO #Customers VALUES (6, 'Kathy', null)
INSERT INTO #Customers VALUES (7, 'Kim', 65443)

-- QUERY TO DISPLAY NON-NULL Values

SELECT ID, cname, COALESCE(pincode,0) as pincode FROM #Customers

As you observe, COALESCE is used which returns the first nonnull expression among its arguments. So if the pincode is null, it returns the first nonnull expression, i.e 0.