ISNULL ,COALESCE AND NULLIF TO TACKLE NULL VALUES

ISNULL(ARGUMENT1,ARGUMENT2)

COALESCE(ARG1,ARG2,[ARG3,ARG4,])

--isnull /*They are like inline if statement

if argument1 is null

return argument2

else

return argument1;

end if--only for isnull

*/

--Colaesce[allows you to extend after argument2....]
/*
if argument1 is null
return argument2
else if argument2 is null
return argument3
*/

isnull(middlename,' ') --if middle name is null replace by ' ' 



=====================COALESCE===============================

select * from dbo.main

select address_id,coalesce(distance,hub_id,0) from main

===================NULLIF========================================


--if argument1 = argument2 then return null
--else return argument1


select nullif(0,0)

select nullif(0,4)

select * from dbo.main

select address_id,hub_id,location,
address_id/hub_id from main

select address_id,hub_id,location,
address_id/ nullif(hub_id,0) from main

select address_id,
hub_id,
location,
isnull(address_id/ nullif(hub_id,0),address_id) as ratio1,
address_id/ISNULL(nullif(hub_id,0),address_id) as ratio2
 from main