Join Class

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	
	[StuId] ASC

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 
sql isnull example

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 example

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".

SQL coalesce example

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

SELECT COALESCE (NULL,10,12,15,24) --10

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.

Hire SQL Developer
Sql isnull nullif coalesce function
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
function check null in sql query
MS SQL Examples | Join MS SQL Course