SQL - Coalesce Function - Single Row Null-Related Function

CoalesceSYNTAX : coalesce(expr1,expr2, ... , exprn)if expr1 is not null then return expr1else if expr2 is not null then return expr2 . . .else if exprn is not null then return exprnelse return default valueNote:- 1. All the arguments should have same datatypes.2. You have to manually make it same using conversion functions whereever required.PurposeCOALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.


create table student
(id number,
name varchar2(20),
phone number(10),
email varchar2(30),
address varchar2(30));

insert into student values (1,'Anmol',9876543210,'[email protected]','Somewhere in Nagpur');
insert into student values (2,'Sahil',null,'[email protected]','Some nagar in nagpur');
insert into student values (3,'Saif',null,null,'Nagpur Maharastra India');
insert into student values (5,'Saurabh',null,null,null);

commit;


Setup for Coalesce Function