Handling Null Values in SQL- ISNULL, NVL2, IFNULL, Coalesce

There can be situations when if a value is null in sql query, you want to replace it with something else. Different DBs provide you different options

Oracle- PLSQL
select NVL2(teamid, teamid, deptid) allocationid, empid, name from employee; //If team id is available that is returned as allocation id else deptid

IN MS-SQL we have ISNULL
select ISNULL(teamid, deptid) allocationid, empid, name from employee;

Similarly MySQL has IFNULL

Another option is COALESCE, this works for both Oracle and MS-SQL. This is slightly different from others in implementation that it makes columns to be skipped unless first non-null value is found

select COALESCE(teamid, deptid, organizationid) allocationid, empid, name from employee;

Leave a Reply