What do COALESCE and NULLIF actually do, and why should you be careful about how you use them?
They do two opposite jobs, and together they solve most of the problems that come with missing values.
COALESCE takes a list of values and gives back the first one that is not missing. You can give it two values or you can give it five. It is the tool for saying "use this one, and if it is missing use that one".
NULLIF takes two values and gives back the first one, unless the two are equal, in which case it gives back a missing value. It is the tool for saying "if these two are the same, treat it as if we did not know".
-- COALESCE: the first value that is not missing.
SELECT COALESCE(city, 'Unknown') AS city_label FROM customers;
-- NULLIF: a missing value when the two are the same.
-- This stops a division by zero.
SELECT amount / NULLIF(quantity, 0) AS unit_price FROM sales_lines;
So why should you be careful about them? Four reasons.
The first one is that COALESCE on a column in the WHERE clause hides the rows that you were probably trying to find. If you write WHERE COALESCE(status, 'open') = 'open', then the rows with no status are suddenly included, and a report that was meant to show the open orders now shows the missing ones too.
The second one is that COALESCE on a column stops an index from being used, because the engine has to work out the value for every row before it can compare it. That is the same rule as any other function wrapped around a column.
The third one is that COALESCE in the select list changes what the number means. If you turn a missing price into a zero, then the report has a zero in it and somebody will add that zero to a total. A missing value and a zero are different facts, and COALESCE makes them the same.
The fourth one is that NULLIF with a zero divisor only works when the zero is exactly zero. If the denominator can be a very small number instead, the division still gives an enormous answer, and the function has not helped.
So the rule to remember is this. Use COALESCE when you want a value to show, and never in a WHERE clause unless you really mean to include the missing rows. Use NULLIF to turn a dangerous value into a missing one, so that the answer is missing rather than wrong.
The next question in this topic puts both of them to work.