Other uses for NULLIF

There are some features in the database that are understandably difficult to first come across. Unless you have a specific need to fulfil, or you aren't the type who meanders through the new features guide, or you're just relatively new to Oracle, you may not have come across useful day-to-day features & concepts such as WITH, DBMS_JOB or pipelining. (I just picked a few random ones that came to mind...)

There are also abundant single row functions that are available, and some of them are easy to use, but sometimes no apparent usage immediately stands out. I make it no secret that my favourite online reference manual is the Oracle Database SQL Reference. I have a shortcut to a local version in my browser toolbar. Personally I prefer the 10g layout to the 11g one, but beggars can't be choosers I suppose.

I thoroughly recommend beginners and even the more mature programmers to peruse this manual, in particular the chapter on SQL Functions. I believe awareness is often the key to writing a successful application - or at least not re-inventing the wheel.

So to stop beating around the bush, what about the NULL-related functions, specifically NULLIF. Have you used it?
The manual illustrates an example showing those employees whose job has changed since they were hired. If their job hasn't changed, then null is shown.

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

LAST_NAME                 Old Job ID
------------------------- ----------
De Haan                   AD_VP
Hartstein                 MK_MAN
Kaufling                  ST_MAN
Kochhar                   AD_VP
Kochhar                   AD_VP
Raphaely                  PU_MAN
Taylor                    SA_REP
Taylor
Whalen                    AD_ASST
Whalen

What about if an employee record has multiple e-mail address columns - an alternative point of contact. In some scenarios/records this could potentially be unclean, so instead of coding with these older statements:

CASE WHEN email_address != email_address_alternate
THEN email_address_alternate
ELSE null
END email_address_alternate

DECODE(email_address, email_address_alternate, null, email_address_alternate) email_address_alternate

We can use a tidier:

NULLIF(email_address_alternate, email_address)

A similar situation applies to a mailing/delivery/home address (albeit ideally on a separate table) or a work/mobile/home number, but in this case the COALESCE function is more appropriate. I plan a blog entry on this function in the near future, along with performance considerations.

Perhaps you are performing a data load and you need to ignore certain indicative data coming through:

NULLIF(surname, 'INACTIVE')

The final scenario that I've started to use regularly is when faced with the old mathematical enigma of dividing by zero. (The wiki entry is a thrilling read, almost as fun as the images returned by google search)

Instead of facing this throughout a report:

select 1/0 from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

We can do this to return null where price is 0 instead of an error.

SELECT (price-cost)/NULLIF(price,0) gp FROM sales;

Sweet.