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