Snowflake NVL vs NVL2 function | SQL

DIFFERENCE BETWEEN  NVL AND NVL2 FUNCTION

---

 This post explains the difference between NVL and NVL2 functions in Snowflake.

The NVL and NVL2 functions are also compatible with other databases like Teradata, Mysql, Postgresql, Oracle, PLSQL, Amazon Redshift.

---

NVL FUNCTION

The NVL function will return the default value if the in- parameter is NULL.

Syntax: NVL ( <in-param>,<out-param>)

• If the in-param is NULL then it will return the out-param value.
• If the in-param is NOT NULL then it will return the in-param value itself.

Difference between NVL and NVL2 function

NVL2 FUNCTION

The NVL2 function will return the first out-parameter if the in-parameter is NOT NULL and return the second parameter if the in-parameter is NULL.

Syntax: NVL2( <in-param>,<out-param-1>,<out-param-2>)

• If the in-param is NULL then it will return the out-param-1 value .
• If the in-param is NOT NULL then it will return the in-param-2 value itself.

Difference between NVL and NVL2 function

NVL VS NVL2 FUNCTION

Features	NVL	NVL2
Syntax	NVL ( <in-param>,<out-param>)	NVL2( <in-param> ,<out-param-1> ,<out-param-2>)
Example	[1] NVL(NULL,'It is NULL') [2] NVL('99','It is NULL')	[1] NVL2('99','It is Not NULL' ,'It is NULL')
Number of IN parameter	1	1
Number of OUT parameter	1	2
If IN parameter is NULL	Returns <out-param> i.e [1] 'It is NULL'	Returns <out-param-1>  i.e [1] 'It is Not NULL'
If IN parameter is NOT NULL	Returns  <in-param>  i.e [2] '99'	Returns <out-param-2>  i.e [1] 'It is NULL'

 

NVL vs NVL2 function | SQL

---

Read More :

• What does count 1 mean in SQL?

---

• How to include a single quote in a SQL query?

---

• Difference between WHERE and HAVING clause

---

• Create a table from an existing table

---