Are you familiar with the word “null”? Some might say null means zero or associates with nothing. In the data and statistics world, null usually means something. But Null can hold various meanings in different settings. Say, in specifically SQL and Excel, Null indicates different meanings in both of them. Let’s see the differences and how to handle Null in each tool!
Null in SQL
Null in SQL holds a value that does not exist in the database. But, note that it is not a zero value or an empty string. This null value usually points to an unknown value that is not inserted in the database but this unknown value cannot be assumed as a zero value or a field with empty spaces. Nulls can be in a field where we still cannot get the data for it, for instance, when an order has been shipped to a customer but not delivered yet, the shipping days will be Null until the order has arrived. In other cases, maybe some people do not have a second phone number, thus, Null can be inserted in this field too.
Test Null Values in SQL
The usual comparison operators like =,<,>,<> are not suitable to filter out Null rows. Instead, we have to use IS NULL and IS NOT NULL.
1. IS NULL: Results in rows that contain Null values
The above query will yield all rows with Null in the postal_code column. The output is:
2. IS NOT NULL: The opposite of IS NULL
This query will result in all rows without Null in the postal_code column. The output is:
Handling SQL NULL values with Functions
SQL can handle Null values with a number of functions.
1. COALESCE (): Replace Null values with a specified value.
This function will ask the query to replace all the Nulls in postal_code column with “No Postal Code”. The output would be:
2. CASE(): Replace Null values with a specified value.
Is Null in SQL useful?
Overall, Null values in SQL are useful for representing real-life situations where most of the times, some information is absent, hence Null can be used as the placeholder. But they should be used accordingly as to avoid low value information.
Null could also be used as itself in the field where no value is available and it will be understood by every programmer as the default placeholder.
Null in Excel
Unlike SQL, Null in Excel means error. It occurs when there is something wrong with the formula. It comes down to two factors:
- Wrong range in the formula
- Typo in the formula
How to fix Null in Excel?
Let’s take a look at this table:
As we can see here, there is a Null in the column Total Miles Covered. If we look at the formula used, the colon (:) is missing. The correct way to write this is =SUM(A1:A8). The outcome is actually:
This is the case of typo that is resulting in wrong formula for the function SUM. Although Null in Excel is a simple error that occurs due to typing errors but it could be avoided by writing the formula carefully. This could avoid any problems in the analysis section later.