For an instructor lead, in-depth look at learning SQL click below.
In the world of SQL programming, handling NULL values gracefully is crucial for building reliable, bug-free applications. Two key functions that the SQL language provides us to deal with NULLs are COALESCE and NULLIF.
COALESCE function
The COALESCE function is a means by which we can manage NULL values by getting the first non-NULL value in a list. Essentially, it’s like saying “if the first expression is NULL, try the second one, if that’s NULL, try the third one, and so on”. When all expressions evaluate to NULL, COALESCE return NULL.
Let’s have a look at an implementation. Here’s a basic example of COALESCE:
|
1 2 3 |
SELECT COALESCE(NULL, 'second', 'third'); |
In this case, since the first argument is NULL, it will return ‘second’ because it is the first non-NULL value in the list.
NULLIF function
The NULLIF function takes two arguments and returns NULL if they are equal. If they’re not, the function returns the first argument. In short, it’s a handy way to convert certain values to NULLs if needed.
Let’s consider the following example:
|
1 2 3 |
SELECT NULLIF('Apple', 'Apple'), NULLIF('Apple', 'Orange'); |
In the first function call, NULLIF returns NULL because ‘Apple’ is equal to ‘Apple’. In the second call, NULLIF returns ‘Apple’ because ‘Apple’ is not equal to ‘Orange’.
Conclusions
COALESCE and NULLIF are powerful functions in SQL. COALESCE allows us to manage NULLs by substituting them with another value, while NULLIF helps turn specific values into NULLs. Understanding them well improves code readability and reliability when dealing with NULL values and boosts SQL functionality.
Further Reading
There’s a lot more to SQL than COALESCE and NULLIF. As you continue to explore SQL’s capabilities, keep practicing and experiment with different functions to get the most out of this flexible language.