In this article, we will review certain SQL functions and how they can be used. You will also check some code examples below. So here’s a brief overview:
COALESCE returns the first non-null parameter among all parameters passed to it. If all parameters are null then it’ll return null. The COALESCE() function in SQL is beneficial to manage NULL values. The NULL values are substituted with the user-entered value throughout the declaration value assessment procedure. The Coalesce function assesses the value in a particular hierarchy and consistently gives the first not the null value from the specified expression index. It gives the data type of value with the most elevated data type priority.
If all values are non-null, the output is classified as non-nullable. A value that includes a subquery is assessed and the subquery is considered twice. This output is in adherence with the SQL benchmark. In either possibility, various consequences can be produced between the primary evaluation and impending evaluations.
Its syntax is as follows.
COALESCE(arg1, arg2 [,argn])
Let’s take an example to get things clear. Suppose we have a customer table which is having contact no’s like office-phone, mobile-no, home-phone, and email. We want to give our sales guys a list of customers with the best available contact no. What I mean by “best available contact no” is if the office phone is not present in our database then we’ll return the mobile phone. If a mobile phone is also not available then we’ll return a home phone. If the home phone is also not available then we’ll return the email id. Below is the SQL query for that.
Now let’s analyze the results. In the case of “Gigi N Matthew”, Office mobile no is present, so it is selected. In the case of “Michael Raheem” we didn’t have office contact no, but mobile no was present, so it selected mobile no.
Now if we look at the contact no of “Thierry B D’Hers”, it is returning a blank value. Although he’s having an email address. The reason is that he’s having a blank string as office contact no, which is not NULL.
So to deal with this scenario, we can use the NULLIF function. Let’s dive into it:
NULLIF returns a null value if both arguments are the same. This function in SQL is utilized to study if the two identified values are similar or not. This function yields NULL if the entered two values are identical. It is also used to produce the first value if the two delivered values are not equal. This function comes under cutting-edge SQL features. It obtains two parameters i.e, the first value, and the second value.
NULLIF is identical to an explored CASE expression in which the two values are identical and the consequential value is NULL. It is highly advised that this function should not be utilized with time-dependent functions like RAND(), within a NULLIF function. This could drive the function to be considered twofold and to yield various outcomes from the two requests.
Its syntax is as follows:
Below is our modified example with the use of NULLIF.
Now, we have contact no of “Thierry B D’Hers”, but for customer “Janice M Galvin” we received NULL output. It’s because we don’t have any information available. So we might want to change out query so instead of returning NULL we can have informative text like ‘’ For this we can use ISNULL function.
ISNULL function has two arguments, the first argument is a variable or expression to evaluate, and the second argument is a value that we want to return if the first argument is NULL. It is used to return the value provided, in case the entered value is NULL. Also, in case the entered value is not NULL then it yields the entered value. All the above-mentioned three functions are extremely important in advanced SQL queries. These functions are extremely valuable in handling NULL values. All three functions can be used in sub-queries and can be used effectively in designing robust database applications.
Its syntax is as follows:
Following is our modified example with the use of the ISNULL function.
Now, we got desired output.