NULLIF has been introducde quite a while ago (Oracle 9) but still this function is not well known to the community.
SYNTAX : NULLIF(expr1,expr2)
Nullif compares expr1 and expr2. If they are equal, then NULL is returned otherwise expr1. We cannot specify the literal NULL for expr1.
So where can this function be used?
Eliminiate Division by Zero:
WITH sales (prod, thisyear, lastyear) AS (SELECT 'Monitor' , 50, 25 FROM dual UNION ALL SELECT 'Keyboard', 125, 0 FROM dual UNION ALL SELECT 'Mouse' , 35, 40 FROM dual UNION ALL SELECT 'Desktop' , 0, 25 FROM dual UNION ALL SELECT 'Laptop' , 10, 3 FROM dual) SELECT prod, thisYear, lastYear , ROUND(thisYear * 100 / NULLIF(lastYear,0),2) AS SalesPerc FROM sales / PROD THISYEAR LASTYEAR SALESPERC -------- ---------- ---------- ---------- Monitor 50 25 200 Keyboard 125 0 Mouse 35 40 87.5 Desktop 0 25 0 Laptop 10 3 333.33
Remove not needed concatenation seperators:
Add first letter of the middle name followed by a dot if a middle name exists.
WITH pers (firstname, middle, lastname) AS (SELECT 'Kurt' ,'Heinrich','Meier' FROM dual UNION ALL SELECT 'Hubert',NULL ,'Huber' FROM dual) SELECT firstname || NULLIF(' '||SUBSTR(middle,1,1) || '.',' .') || ' ' || lastname AS fullname FROM pers / FULLNAME --------------- Kurt H. Meier Hubert Huber
Suppress values above (or below) a given margin:
Do not show salaries greater/equal 3000.
SELECT ename, NULLIF(sal,GREATEST(sal,3000)) AS sal FROM emp / ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT KING TURNER 1500 ADAMS 1100 JAMES 950 FORD MILLER 1300
As you can see there are multiple opportunities to make use of this smart little function.