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-tech.blogspot.c...

[@santoshadhikari9933]

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

[@RameshYadav-ei3kd]

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

[@martinsuresh5048]

very helpful information hatsoff sir

[@anilb5557]

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

[@SivaAcademy]

Welcome Anil 🙂

[@harekrushna09]

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

[@SivaAcademy]

Welcome bro

[@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

[@devanaidu9406]

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

[@SivaAcademy]

Welcome 🙏 🙏

[@kumarakrish3976]

Great explantions sir this content helped in interviews superbbb 😊😎

[@SivaAcademy]

My pleasure 💐💐👍🙏

[@niranjandhavalikar]

Thank You!!! Amazing explanation

[@SivaAcademy]

Thanks for your comments 🙏 🙏🙏

[@4ashutosh]

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

[@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?

[@snehalr4312]

As always in detail supeerrb !!!!

[@SivaAcademy]

My pleasure bro

[@i.ashokreddy8548]

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

[@SivaAcademy]

Welcome bro

[@bharatk212]

Very useful information.. Thanks for this video

[@SivaAcademy]

Welcome bro

[@devanaidu9406]

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

[@mallikarjunam3239]

Thank you so much Sir for your information

[@SivaAcademy]

🙏🙏Welcome

[@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.

[@iswaryas5321]

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

[@SivaAcademy]

Thank you and welcome

[@pritisingh8639]

Nice video ...very well explained

[@SivaAcademy]

Welcome 🙏

[@ragulraina1422]

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

[@SivaAcademy]

Welcome bro

[@dineshkumar-vp9dm]

What is different between varchar2 and varchar explain

[@sannidhirao1281]

Thank you som uch for the easy explanation

[@SivaAcademy]

Welcome

[@sureshraina321]

you nailed it sir thank you sir

[@SivaAcademy]

Thank you

[@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

[@ashishmishra3797]

Best Explanation!

[@SivaAcademy]

Thank you 💐

[@ShaikAbdulFayazYCE]

excellent videos thank you sir

[@SivaAcademy]

Welcome bro

[@bidhubhusanpolai3695]

Nice understanding sir

[@SivaAcademy]

My pleasure 💐

[@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

[@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.

[@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?

[@pavanjoshi2356]

Nice explained

[@SivaAcademy]

🙏🙏Thank you

[@mupparthiuma5798]

hi sir..can you take online classes
?

[@M_M_Vlogs]

can we get this as pdf ?

[@yuvrajb4696]

Jay Ho Jay Hind 🇮🇳

[@bharathipandian45]

I need unix interview question ans production support related interview questions

[@vivektarab16]

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

[@giridandi7480]

Nice

[@mohantadibyajyoti]

Could you please make video on DBMS_parallel_execute and DBMS_jobs procedure.

[@SivaAcademy]

Sure, please stay tuned

[@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

[@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

[@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.....

[@vishnumishra2219]

thnkx a lot

[@SivaAcademy]

Welcome bro

[@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

[@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

[@saptarshikonar3149]

very good explanation

[@SivaAcademy]

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

[@udayreddybarri6642]

Pls send for and forall

[@bharathipandian45]

I need unix interview question ans production support related interview questions