For an instructor lead, in-depth look at learning SQL click below.
In SQL, NULL represents missing or unknown data. There will be scenarios where we want to replace these NULL values with some default value or any other value that makes sense according to the business context.
The SQL COALESCE() function or the ISNULL() function can help in substituting NULL values with another value.
Understanding COALESCE() Function
The COALESCE() function in SQL returns the first non-NULL value in a list. Here’s how you can use it:
|
1 2 3 4 5 6 |
-- COALESCE() Function SELECT ProductName, UnitPrice, COALESCE(DiscountedPrice, UnitPrice) AS SellingPrice FROM Products; |
In this example, if the DiscountedPrice is NULL, then the UnitPrice will be displayed in the result set as the SellingPrice.
Understanding ISNULL() Function
The ISNULL() function works quite similar to the COALESCE() function. However, ISNULL() is specific to MS SQL Server. It allows you to replace NULL with a specified replacement value. See the example below:
|
1 2 3 4 5 6 |
-- ISNULL() Function SELECT ProductName, UnitPrice, ISNULL(DiscountedPrice, UnitPrice) AS SellingPrice FROM Products; |
Just like in the COALESCE() function, ISNULL() will return the UnitPrice if DiscountedPrice is NULL.
Warning
The COALESCE() function is more portable because it’s a part of SQL standard, while the ISNULL() is specific to MS SQL Server. Always bear this in mind when developing SQL scripts.
Summary
In conclusion, handling NULL values effectively is key to building robust data analytics infrastructure and SQL provides us with handy tools like COALESCE and ISNULL to achieve the same. Always choose the one that best fits your use case and the DBMS (Database Management System) you are using.