General / NVL functions

+++++++++++++++++++++++++++++++++++  +++++++++++++++++++++++++++++++++++ 

NULL functions help you handle missing or unknown values. Here's a list of the most commonly used NULL-related functions

nvl

nvl2

nullif

coalesce

DECODE

case

LISTAGG

++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++ 

NVL(expr1, expr2)

• Replaces NULL in expr1 with expr2.

• select nvl(100,200) from dual;  ---> 100
• select nvl(100, null) from dual;    ---> 100
• select nvl(null, 200) from dual;    ---> 200
• select nvl(null, null) from dual;   ---> (null)   

select first_name, salary, nvl(salary, 500) from emp1  where salary is null;   --only will show who having salary is null, with replacement salary null=500

• John Doe      500
• GRITHIK    500

SELECT  salary, NVL(commission, 0),    (salary*12) + (salary*12*NVL(commission, 0)) annual_salary FROM emp1;;

• 2334.56 6.15 200305.248

salary --> This selects the monthly salary of the employee.

NVL(commission, 0) --------

• NVL() is a null-handling function in Oracle.
• If the commission is NULL, it replaces it with 0.
• This ensures calculations won’t fail or return null when commission is missing.

(salary * 12)-------- This calculates the base annual salary assuming 12 months.

(salary * 12 * NVL(commission, 0))--------

• This calculates the commission-based annual bonus, if applicable.
• If there's no commission, this part returns 0.

annual_salary --------This alias is the total annual salary:

++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++ 

NVL2(expr1, expr2, expr3)

• If expr1 is NOT NULL → returns expr2, else returns expr3.

select nvl2(100,200,300) from dual;  --------- 200

select nvl2(100, null,300) from dual; ---------  (null) 

select nvl2(null,null, 200) from dual; ---------  200

select nvl2(null,200, null) from dual; ---------  (null)   

 

select nvl2(100,null, null) from dual; ---------  (null) ;

select nvl2(null,200, 300) from dual;   ---------  300;

select first_name, salary, nvl2(salary,500, 1000) from emp1 where salary is null ;

• John Doe null   1000
• GRITHIK         null   1000

select first_name, salary, nvl2(salary, 500,null) from emp1 where salary is null    ;

• John Doe   null null
• GRITHIK    null null

SELECT first_name, salary, commission,  NVL2(commission, salary+commission, salary)income

  FROM emp1;

• DIVYA_GN         2334.56      6.15     2340.71
• PRABHU_GN         22.23      0.15     22.38
• SUNNY                 2344.56      1.15     2345.71
• John Doe               null              null         null
• GRITHIK                 null              null          null

++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++ 

NULLIF(expr1, expr2)

• Returns NULL if expr1 = expr2, otherwise returns expr1.

select nullif(100,100) from dual; ---------------// both are same, output is NULL

select nullif(100,null) from dual; ---------------//both are not same, output is 100

select nullif(null, 200) from dual; ---------------//both are not same, ERROR will show

select nullif(100, 200) from dual; ---------------//both are not same, output is 100

SELECT first_name, LENGTH(first_name) "expr1", last_name,  LENGTH(last_name) "expr2",  NULLIF(LENGTH(first_name),LENGTH(last_name))

result FROM emp1;

• DIVYA_GN 8 RANI       4    8
• SUNNY         5 BUNNY       5     null
• John Doe 8 null       null       8

++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++ 

COALESCE(expr1, expr2, ..., exprN)

• Returns the first non-null expression.

select coalesce(100,200,300) from dual ; ---------------//100       

----examines the first expression, if the first expression is not null, it returns that expression, TILL IT'S FINDS "NOTNULL" OF 1ST VALUE

select coalesce(null,null,200,300) from dual ;  ---------------//200

select coalesce(null,null,null,null,200,null, 300) from dual;   ---------------//200

select coalesce(null,null) from dual;   ---------------//null

++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++ 

CASE Expression (for advanced NULL handling)

SELECT 
  CASE 
    WHEN salary IS NULL THEN 'No Salary'
    ELSE 'Salary Present'
  END AS salary_status
FROM emp1;;

++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++ 

IS NULL / IS NOT NULL (for filtering)

SELECT * FROM employees WHERE manager_id IS NULL;

SELECT * FROM employees WHERE salary IS not NULL;