General functions are very useful while working with SQL and PL/SQL. These functions are used for working with null values and can handle any data type. We have 4 functions available in SQL which comes under general functions.
They are,
--> NVL Function
--> NVL2 function
--> NULLIF function
--> COALESCE function
Basically all these functions are used for avoiding wrong results in SQL statements which involves null
values. For example let us say we have two columns in Employee table named Sal and Bonus. Sal column will have values of employee salary and Bonus column will have values of bonus. If we want to find out total sal of employee we need to sum the values from these two columns.
Let us say we have following values stored in employee table.
Emp1 2000 null
Emp2 1500 200
Emp3 3500 null
Now if we write following query on this table result will be as shown.
SELECT ename,sal+bonus from emp;
Result :: ename sal+bonus
Emp1 null
Emp2 1700
Emp3 null
We can see from the result that total sals for employees Emp1 and Emp3 is coming as null. This is because
we have null values stored for these employees in bonus column and in SQL null means nothing. If we add
some value to null then result will be null only. To get correct results in such type of situations we will use
general functions. Let us discuss these functions now.
NVL Function ::
===========
If any column contains null values then arithmetic expressions involving those columns will give inappropriate results as shown in previous example. To avoid this we need to handle those null values. NVL function can be used to handle null values present in the column while using them in SELECT statements.
SYNTAX :: Syntax for NVL function is,
NVL ( column or expression , default value)
Here column or expression will be the one which we are expecting to contain null values and which we want to handle using NVL function.
default value is corresponding to the value which we want to use in case null value is present in that particular column. We have to provide correct default values depending on requirement to get proper results.
In our previous example to get correct total salary we can use 0 as default value. Then query can be written as following.
SELECT ename, sal+nvl(bonus,0) from emp;
No this will give result as,
Emp1 2000
Emp2 1700
Emp3 3500
Like this we can avoid wrong results in SQL statements involving null values.
NVL2 Function ::
============
This function is bit different from NVL function and will be used rarely in SQL. This functions contains 3 arguments and the result will always be decided on first value.
SYNTAX :: Syntax for this function is,
NVL2(col1 or exp1, col2 or exp2 or val1, col3 or exp3 or val3)
Here first argument corresponds to column or expression based on which we want to decide the result and 2nd argument and 3rd argument will give us the results based on first argument.
How this NVL2 function works is if the value of first argument is not null then this function will give second argument value as result. If value of first argument is null then this function will give third argument as result.
For example if we write query on data present in previous example as below,
SELECT ename, NVL2(bonus,sal,5000) from Emp;
Then result will be as shown below.
Emp1 5000
Emp2 1500
Emp3 5000
For Emp1 and Emp3 bonus is null so 5000 will come as result . For Emp2 bonus is not null so sal value which is 1500 will come as result.
This is how NVL2 Function will work.
NULLIF Function ::
==============
This function is also different from other general functions in the way how it works. Basically this function will be having two arguments as inputs and it will produce the results based on those two arguments.
SYNTAX:: Syntax for this function is,
NULLIF (col1 or exp1, col2 or exp2)
Here both the arguments are responsible for the result. This function will compare the values of the two arguments and returns null if both are equal and returns first argument value if both are not equal.
For example if we write query as,
SELECT NULLIF(col1,col2) from tab;
if values of col1 and col2 are 20 and 30 then result will be 20
if values of columns is 10 and 10 then result will be null.
So, this is how NULLIF function will work.
COALESCE Function ::
=================
This function will work same as the NVL function. But only difference is that it will be having multiple arguments ( more than 2 unlike NVL). It will return first not null value from input argument list .
SYNTAX :: Syntax for this function is,
COALESCE(arg1, arg2, arg3, arg4,...)
Here we can pass any number of columns or expressions as arguments to this function. This will give us the first not null value from left of the argument list as result.
For example,
If values of arguments is like null,null,3,4.. then result will come as 3
If values are like 1,null,null,.... result will be like 1.
So, this works similar to NVL function only but the only difference is that it will have multiple inputs.
So, these are the different General Functions which are available in SQL. Among all these functions NVL is the most used function in SQL queries.
They are,
--> NVL Function
--> NVL2 function
--> NULLIF function
--> COALESCE function
Basically all these functions are used for avoiding wrong results in SQL statements which involves null
values. For example let us say we have two columns in Employee table named Sal and Bonus. Sal column will have values of employee salary and Bonus column will have values of bonus. If we want to find out total sal of employee we need to sum the values from these two columns.
Let us say we have following values stored in employee table.
Emp1 2000 null
Emp2 1500 200
Emp3 3500 null
Now if we write following query on this table result will be as shown.
SELECT ename,sal+bonus from emp;
Result :: ename sal+bonus
Emp1 null
Emp2 1700
Emp3 null
We can see from the result that total sals for employees Emp1 and Emp3 is coming as null. This is because
we have null values stored for these employees in bonus column and in SQL null means nothing. If we add
some value to null then result will be null only. To get correct results in such type of situations we will use
general functions. Let us discuss these functions now.
NVL Function ::
===========
If any column contains null values then arithmetic expressions involving those columns will give inappropriate results as shown in previous example. To avoid this we need to handle those null values. NVL function can be used to handle null values present in the column while using them in SELECT statements.
SYNTAX :: Syntax for NVL function is,
NVL ( column or expression , default value)
Here column or expression will be the one which we are expecting to contain null values and which we want to handle using NVL function.
default value is corresponding to the value which we want to use in case null value is present in that particular column. We have to provide correct default values depending on requirement to get proper results.
In our previous example to get correct total salary we can use 0 as default value. Then query can be written as following.
SELECT ename, sal+nvl(bonus,0) from emp;
No this will give result as,
Emp1 2000
Emp2 1700
Emp3 3500
Like this we can avoid wrong results in SQL statements involving null values.
NVL2 Function ::
============
This function is bit different from NVL function and will be used rarely in SQL. This functions contains 3 arguments and the result will always be decided on first value.
SYNTAX :: Syntax for this function is,
NVL2(col1 or exp1, col2 or exp2 or val1, col3 or exp3 or val3)
Here first argument corresponds to column or expression based on which we want to decide the result and 2nd argument and 3rd argument will give us the results based on first argument.
How this NVL2 function works is if the value of first argument is not null then this function will give second argument value as result. If value of first argument is null then this function will give third argument as result.
For example if we write query on data present in previous example as below,
SELECT ename, NVL2(bonus,sal,5000) from Emp;
Then result will be as shown below.
Emp1 5000
Emp2 1500
Emp3 5000
For Emp1 and Emp3 bonus is null so 5000 will come as result . For Emp2 bonus is not null so sal value which is 1500 will come as result.
This is how NVL2 Function will work.
NULLIF Function ::
==============
This function is also different from other general functions in the way how it works. Basically this function will be having two arguments as inputs and it will produce the results based on those two arguments.
SYNTAX:: Syntax for this function is,
NULLIF (col1 or exp1, col2 or exp2)
Here both the arguments are responsible for the result. This function will compare the values of the two arguments and returns null if both are equal and returns first argument value if both are not equal.
For example if we write query as,
SELECT NULLIF(col1,col2) from tab;
if values of col1 and col2 are 20 and 30 then result will be 20
if values of columns is 10 and 10 then result will be null.
So, this is how NULLIF function will work.
COALESCE Function ::
=================
This function will work same as the NVL function. But only difference is that it will be having multiple arguments ( more than 2 unlike NVL). It will return first not null value from input argument list .
SYNTAX :: Syntax for this function is,
COALESCE(arg1, arg2, arg3, arg4,...)
Here we can pass any number of columns or expressions as arguments to this function. This will give us the first not null value from left of the argument list as result.
For example,
If values of arguments is like null,null,3,4.. then result will come as 3
If values are like 1,null,null,.... result will be like 1.
So, this works similar to NVL function only but the only difference is that it will have multiple inputs.
So, these are the different General Functions which are available in SQL. Among all these functions NVL is the most used function in SQL queries.
