Handling Null values in Oracle | Oracle interview question difference between null functions |Tamil

#Oracle#NVL#NVL2#NULLIF#COALESCE ======================================================== 🌕Want to join WhatsApp Group? (Only technical discussion, no spam messages, no promotion msg) 📌Informatica PowerCenter https://github.com/nicitacademy/Informatica 📌Oracle-SQL https://github.com/nicitacademy/Oracle-SQL 📌IICS https://github.com/nicitacademy/IICS ======================================================== Handling Null values in Oracle: ================================ NVL - 2 arg NVL2 - 3 arg Nullif - 2 arg Coalesce - n arg NVL function will check whether the first input parameter is null, if first input parameter is null then the function returns the second parameter value as output. NVL2 function will check the first parameter, and return second parameter if the first parameter is not null, otherwise returns third parameter. NULLIF will compare the input parameters ( first and second parameter ), and returns NULL of both are same, otherwise returns the first parameter value. COALESCE, returns the first not null expression in the given input parameters nvl function in sql what is nvl function in oracle what is the use of nvl2 function in oracle what is the use of nvl function in oracle Oracle NVL() Function By Practical Examples - Oracle Tutorial Oracle / PLSQL: NVL Function How to use the nvl function in Oracle Oracle NVL Function Explained with Examples NVL(arg1,arg2) if arg1 is null ==arg2 if arg1 is not null == arg1 select NVL(5,6) from dual; -- 5 select NVL(null,6) from dual; --6 select * from employees; select employee_id,salary,commission_pct,salary+(salary*commission_pct) total_salary from employees; select employee_id,salary,commission_pct,salary+(salary*nvL(commission_pct,0)) total_salary from employees; NVL2(arg1,arg2,arg3) if arg1 is null --- arg3 if arg1 is not null -- arg2 select nvl2(4,8,12) from dual; --8 select nvl2(null,8,12) from dual; -- 12 create table employee ( employee_id number, emp_name varchar2(30), allocation_id number(10), location varchar2(30) ); SELECT employee_id,emp_name, NVL2(allocation_id, 'Allocated', 'Waiting for project') allocation_status FROM EMPLOYEE; ----------------------------------------------------------- nullif(arg1,arg2) if arg1=arg2 --- null if arg1 != arg2 --- arg1 select nullif(5,8) from dual; --5 select nullif(8,8) from dual; -- null CREATE OR REPLACE FUNCTION FUNCTION_DIV(A NUMBER, B NUMBER) RETURN NUMBER IS BEGIN RETURN 8/0; END; select FUNCTION_DIV(4,0) from dual; FUNCTION_DIV(5,0) will raise ORA-01476:divide by zero exception. CREATE OR REPLACE FUNCTION F_DIV(A NUMBER, B NUMBER) RETURN NUMBER IS BEGIN RETURN (A/NULLIF(B,0)); END; select FUNCTION_DIV(4,2) from dual; FUNCTION_DIV(5,0) would return NULL, instead of raising exception. ----------------------------------------------------------- coalesce(arg1,arg2,arg3.. ..arg_n) -- It will return first not null value COALESCE -- It will always return first not null value select commission_pct,manager_id,department_id from employees; select commission_pct,manager_id,department_id, COALESCE(commission_pct,manager_id,department_id,0) from employees; select coalesce(mobile_no,office_no,resi_no,'no_phone_number') from dual;