what is the difference between Coalesce and nullif

Question

I am currently working on an SSRS report that will need to display a Dr's ID based on either their DEA number or NPI number. I need to be able to do a coalesce on DEA and NPI to find the non-unknown one, which I will then add code to the SSRS report that will display a hover over stating whether the "DEA Number" or "NPI Number" is being displayed.

Would the following SQL accomplish this on the back-end? From what I have read online it seems like nullif and coalesce are very similar and was wondering what the underlining difference is and if they could be used together to accomplish this requirement.

coalesce(nullif(convert(varchar,NationalProviderIdentifier),'0'), DEANumber) as 'Dr Id'

Answer 1

They are more or less unrelated.

coalesce() takes a list of values and returns the first non-null value (or null if all values are null).

nullif() takes two values and returns the first value, except it returns null if the values are equal.

---

If the two were converted to case statements, they would be:

coalesce:

case
    when value1 is not null then value1
    when value2 is not null then value2
    ... etc
    else null
end

nullif:

case when value1 = value2 then null else value1 end