LNNVL Justification

LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of returning the opposite of the truth condition rather than just handling the NULL values?

For example, suppose you have an Emp table with StartCommission and CurrentCommission columns which may contain nulls. The following returns only rows with neither value null:

SELECT * FROM Emp WHERE StartCommission = CurrentCommission;

If you wanted to include rows where either commission is null you could do something like this:

SELECT * FROM Emp WHERE StartCommission = CurrentCommission 
OR StartCommission IS NULL OR CurrentCommission IS NULL;

It would seem like a function would exist to shorten this syntax, but using LNNVL returns all the non-equal records and all the records with nulls.

SELECT * FROM Emp WHERE LNNVL(StartCommission = CurrentCommission);

Adding NOT to this only returns rows without nulls. It seems to me that the desired functionality for this case would be to keep true conditions true, false conditions false, and have unknown conditions evaluate to true. Have I really created a low use case here? Is it really more likely to want to turn unknown into true, true into false, and false into true?

create table emp (StartCommission Number(3,2), CurrentCommission Number(3,2));
insert into emp values (null,null);
insert into emp values (null,.1);
insert into emp values (.2,null);
insert into emp values (.3,.4);

It is a weird function with a weird history - but then so is nvl2 weird. lnnvl is basically an is not true operator - no doubt it can be put to good use like nvl2 can, but when you have to look a function up every time you use it to remind you exactly what it does, you are left wondering if it is best to stick to nvl, coalesce, decode and nullif along with case expressions, which are more intuitive

StackExchange Database Administrators Q#2701, answer score: 7