## Tip of the day: use LNNVL function

Recently, I came across LNNVL Oracle function and decided to assess its usability.

According to Oracle documentation, it “provides a concise way to evaluate a condition when one or both operands of the condition may be null.

Let’s see a couple of problems this function can be applied to:

For each department count the number of employees who get no commission.

There are over 10 possible solutions that I am aware of, I will focus only on a couple where we can leverage LNNVL function.

Solution #1. Filter with LNNVL in WHERE clause:

```SELECT deptno, COUNT(*) cnt
FROM scott.emp
WHERE LNNVL(comm>0)
GROUP BY deptno
ORDER BY 1;
```

Result:

```DEPTNO        CNT
------ ----------
10          3
20          5
30          3
```

In the above query, LNNVL(comm>0) filter is equivalent to: (comm<=0 OR comm IS NULL) and since comm cannot be negative, we can say that it is the same as NVL(comm,0)=0.

According to that same Oracle documentation, LNNVL “can be used only in the `WHERE` clause of a query.” This does not seem to be true, at least in 12g+ releases:

Solution #2. Using LNNVL in conditional aggregation:

```SELECT deptno, SUM(CASE WHEN LNNVL(comm>0) THEN 1 ELSE 0 END) cnt
FROM scott.emp
GROUP BY deptno
ORDER BY 1;```

LNNVL can also be used with IN operator. We will illustrate it with a solution (one of the many) to the following problem:

List all employees who is not a manager of somebody else.

```SELECT empno, ename, deptno, job, mgr
FROM scott.emp
WHERE LNNVL(empno IN (SELECT mgr FROM scott.emp))
ORDER BY 1;```

The following boolean expression defines a condition to see managers only:

`empno IN (SELECT mgr FROM scott.emp)`

We need the opposite one that handles NULLs – as mgr is a nullable column. And this is where LNNVL helps us.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/