Check null in sql query with nullif and coalesce

There are various ways we can check null in sql query, in this post we learn how to use sql isnull, nullif and coalesce function in sql query.

In order to experiment different ways to check null data in sql query, first we create some tables and insert some data, so we can write query to check null.

CREATE TABLE [dbo].[tbStudent](
	[StuId] [bigint] IDENTITY(1,1) NOT NULL,
	[Firstname] [varchar](50) NOT NULL,
	[Lastname] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[Mobile] [varchar](50) NULL	
PRIMARY KEY CLUSTERED 
(
	[StuId] ASC
)) ON [PRIMARY] 

Simple way to fetch data where some column value is null, is like where Mobile is null .

select * from [dbo].[tbStudent] where Mobile is null 

Now we see how to use sql isnull function. We check same above condition where mobile number is null, and we replace the above null value with some default value. isnull(Column-name,'default-value') alias-name

Select *, isnull(Mobile,'808080888') Mobile from [dbo].[tbStudent]

Sql NULLIF function is used for comparison of two expressions, the function returns null if both are equal.

SELECT price, NULLIF(price, oldprice) AS oldprice FROM [tbStock];  

Nullif will return "null" if both value is same, else return the first value, for example SELECT NULLIF(10,10) AS Result; will return "NULL", and select NULLIF(10,70) AS Result1; will return "10".

The coalesce coalesce() function returns the first non-null value from query.

SELECT COALESCE (NULL,'X','Y')  --x
SELECT COALESCE (NULL,10,12,15,24) --10
SELECT COALESCE (NULL,50,NULL,NULL) --50

We can use coalesce function as case in sql query, here is an real-time example of coalesce function in sql query, just think we are fetching student records including mobile number from sql table, but there are some records where students mobile number are missing, we want place a default mobile number like ‘80808022’ in query result.

select *, COALESCE (Mobile,'80808022') NewNumber from tbStudent 

Above coalesce function in above sql query will add a new column called “NewNumber”, which will have default number where mobile is null.