NULLIF

=NULLIF Function =

Description of the NULLIF function
===The purpose of the NULLIF function is to compare two functions. As you're comparing two functions and notice that they are equal, then the functions that will be returned are NULL. Then if the functions are not equal, the function returns the first expression. ===


===SELECT last_name, LENGTH (last_name) "Length LN", first_name, LENGTH(first_name) "Length FN", NULLIF(LENGTH(last_name), Length(first_name)) AS "Compare Them" ===


 
 * LAST_NAME || Length LN || FIRST_NAME || Length FN || Compare Them ||
 * cho || 3 || Jeniffer || 8 || 3 ||
 * Tsang || 5 || Jason || 5 || - ||
 * Plumb || 5 || Allison || 7 || 5 ||

Answer to my email:
===//Real-life? The short answer is no. Semi-real-life? A cool example is here: http://www.bennadel.com/blog/579-SQL-COUNT-NULLIF-Is-Totally-Awesome.htm However, in all cases (pun intended), it is better to use CASE than NULLIF (and this probably extends to NVL and NVL2 as well) because it is more self-documenting. So these two are the same: //===


===//In fact, even the docs say that this is the same as CASE: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions107.htm#i1001340 So why do we TEACH nullif? Because someone ELSE is going to use it and we need to know what they did, but NEVER use it yourself. It is not faster, and it is less obvious as to intended function.  //===