Oracle interview question difference between null functions | NVL vs NVL2 vs NULLIF vs COALESCE

Difference between NVL,NVL2, NULLIF and COALESCE
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.
--------------------------------------------------------------------------------------------------------
List of all the SQL and PLSQL interview questions
easy-learning-...

[@santoshadhikari9933]

These 4 5 7 minutes videos are better than reading hours and hours or going through 20 mins videos

[@bharathipandian45]

Hi siva, I have got few more questions 1. Difference between local and global index 2. Before we go for partitioning table what are the things we should analyse 3.where should I declare a procedure which should not be accessed by anyone 4. How to dynamically pass parameters to one cursor from another cursor

[@SivaAcademy]

@Bharathi, it's difficult to explain all of this in few lines, however I try my level best to answer in upcoming video

[@anilb5557]

Complex concepts easy explanation thanks sir keep making such types of videos that helps to many students

[@SivaAcademy]

Welcome Anil 🙂

[@RameshYadav-ei3kd]

I want this type of explanation to all concepts.....thanq.....

[@i.ashokreddy8548]

explanation is awesome and the way of teaching is very good to uderstand for the beginner

[@SivaAcademy]

Welcome bro

[@devanaidu9406]

Hi sir your explanation is very well on each and every video and thank you very much to share with all

[@SivaAcademy]

Welcome 🙏 🙏

[@vivektarab16]

1:06 - NVL
2:02 - In Case both values are NULL
2:46 - NVL 2
4:25 - NULLIF
5:52 - Coalesce

[@devanaidu9406]

Please make partitions, cluster's and ,inline view concepts in detailed sir

[@hemlathaiyer2404]

Hi Thank you for the so much informative videos.. I was asked a question in interview about difference between varchar and varchar2 datatype in oracle. Can you explain the difference please?

[@junkingjunking8477]

Nice videos.
Please include
NULLIF(NULL, NULL)
in this video.
Obviously, the result we know based on previous example but it would be nice to include that.

[@SivaAcademy]

Sure, for subsequent videos I will add all possible scenarios 👍

[@m.s.k5300]

Result null

[@bmanikandan70]

It throw inconsistent datatype..thumb rule for nullif is first parameter should not be null

[@4ashutosh]

Please let me know how many parameters can we put in the COALESCE, GREATEST and LEAST functions ?

[@bharatk212]

Very useful information.. Thanks for this video

[@SivaAcademy]

Welcome bro

[@pruthvisarkar2534]

Write a query to display Salesid , SalesNo ,discount ,price of a mobile. if there is no discount for a mobile the display it as"not applicable"......use COALESCE function...!Sir can u help me with this query?

[@ragulraina1422]

Thanks a lot.. very informative and easy to learn..

[@SivaAcademy]

Welcome bro

[@kalpeshpatil4321]

Can you please make a video on regular expression and type of regular expression with small example in easy way to get it understand.
It will be very thankful, if you make video on this.
Thanks in advance cheers.

[@yuvrajb4696]

Jay Ho Jay Hind 🇮🇳

[@madrasboykumaran]

Great explantions sir this content helped in interviews superbbb 😊😎

[@SivaAcademy]

My pleasure 💐💐👍🙏

[@satrinakelley-jordan6418]

Great explanation, thank you!

[@SivaAcademy]

Thank you

[@mupparthiuma5798]

hi sir..can you take online classes
?

[@dineshkumar-vp9dm]

What is different between varchar2 and varchar explain

[@keerthin9362]

Hi sir, can you explain how to show nulls in all columns if I don't have queried data in a table.

[@kwkw5711]

Union with select null as col1, select col2 from dual

[@harekrushna09]

Thank you Sir, for your great explanation of these functions.

[@SivaAcademy]

Welcome bro

[@martinsuresh5048]

very helpful information hatsoff sir

[@iswaryas5321]

Thank you so much for good explanation video...!!

[@SivaAcademy]

Thank you and welcome

[@BatchelorHub]

What is the result of NULLIF( NULL,NULL)
since two nulls are not equal

[@naveenavnp383]

As he said First argument should not have null. If so it throws error

[@snehalr4312]

As always in detail supeerrb !!!!

[@SivaAcademy]

My pleasure bro

[@mohantadibyajyoti]

Could you please make video on DBMS_parallel_execute and DBMS_jobs procedure.

[@SivaAcademy]

Sure, please stay tuned

[@ravipatisrikanth8331]

select coalsce('siva',1,2,3,4,5) as result from dual; why this one is not execute , tell me the reason

[@SivaAcademy]

Data type of all the parameters should be consistent,
try this query
select coalesce('A','1','2','3','4','5') from dual;

[@ravipatisrikanth8331]

Thank you so much

[@sagarjadhav299]

good
please make video on performance tuning

[@SivaAcademy]

Thanks Sagar, Ya I am in process of creating PT with right examples, WIll be posting soon.

[@M_M_Vlogs]

can we get this as pdf ?

[@ShaikAbdulFayazYCE]

excellent videos thank you sir

[@SivaAcademy]

Welcome bro

[@sannidhirao1281]

Thank you som uch for the easy explanation

[@SivaAcademy]

Welcome

[@pritisingh8639]

Nice video ...very well explained

[@SivaAcademy]

Welcome 🙏

[@manjushreesb7580]

Managements wants to know the manager of each resort.
Display resortid , resort name, manager name and his phone. Sort the output in ascending order of resort id. Give alias to manager name as MANAGERNAME and phone as PHONENO.
If manager is not available then display manager name 'NA'. If phone number is not available then display as 'NA'.
Sir can I get an answer for this please

[@SivaAcademy]

select resortid , resort_name, nvl(manager_name,'NA') as "MANAGERNAME" , nvl(phone,'NA') as "PHONENO"
from table_name
order by resortid asc

[@udayreddybarri6642]

What is difference between for and forall

[@SivaAcademy]

Sure, please stay tuned, will add new video for this

[@bharathipandian45]

I need unix interview question ans production support related interview questions

[@ashishmishraaaa]

Best Explanation!

[@SivaAcademy]

Thank you 💐

[@mallikarjunam3239]

Thank you so much Sir for your information

[@SivaAcademy]

🙏🙏Welcome

[@anandhineema8278]

in real time projects , where its been used.Please xplain bro

[@SivaAcademy]

@Anandhi, "NVL", and "coalesce" functions are widely used, and most of the SQL developers would have used it day to day sql queries, however nullif and nvl2 are rarely used, depends on the functionality.
NVL - will be used when ever you want to have a default value for nulls, for eg, if the date is null, you can consider sysdate as default value.
coalesce - To get the fist not null expression in the given list of values, for eg, NVL( mobile_no1,mobile_no2, LAN_LINE_NO, fax_no,0), to get the fist not null value of given list of contact numbers.....

[@niranjandhavalikar]

Thank You!!! Amazing explanation

[@SivaAcademy]

Thanks for your comments 🙏 🙏🙏

[@devanaidu9406]

Hi sir Please make partitions, cluster's and ,inline view concepts in detailed asap sir

[@SivaAcademy]

Sure, please stay tuned

[@devanaidu9406]

@@SivaAcademy tq sir

[@bidhubhusanpolai3695]

Nice understanding sir

[@SivaAcademy]

My pleasure 💐

[@sureshraina321]

you nailed it sir thank you sir

[@SivaAcademy]

Thank you

[@saptarshikonar3149]

very good explanation

[@SivaAcademy]

@Saptarishi, Thanks for your comment :-), --Siva

[@pavanjoshi2356]

Nice explained

[@SivaAcademy]

🙏🙏Thank you

[@giridandi7480]

Nice

[@udayreddybarri6642]

Pls send for and forall

[@vishnumishra2219]

thnkx a lot

[@SivaAcademy]

Welcome bro

[@vivektarab1959]

1:06 - NVL
2:02 - In Case both values are NULL
2:46 - NVL 2
4:25 - NULLIF
5:52 - Coalesce

[@bharathipandian45]

I need unix interview question ans production support related interview questions