Working with NULL values

1. Working with NULL values

Welcome back! In this video, we'll be talking about working with NULL values.

2. What are NULL values?

So, what are NULL Values? NULL values contain no value, meaning no data. However, they are not the same as 0. For example, arithmetic expressions with NULL evaluate to NULL. Aggregate functions like count often ignore NULL values.

3. Why do we care about null values?

Why do we care about NULL values? Because the data we get in the real-world is messy. Often data in a column is lost, missing, or not applicable to a certain record. We need to handle NULL values when we want to clean data or analyze missing data.

4. Testing if a value is NULL

The equal operator cannot be used to check for NULL values. It will return an incorrect answer. Instead you need to use `IS NULL`. In this snippet, all customers without a recorded lastname will be return. Conversely, to check if something is not NULL, you can use IS NOT NULL. This will return all records with non-null values for lastname.

5. NVL

Oftentimes with NULL values, we want to convert them to non-null values to make data more interpretable, cleaner, or easier to work with. For example, in the employee table, there is a column for a HireDate. When the company was founded, this data was not collected for the first few employees, so their records have a null value for HireDate. To have more complete data, we can convert all null values in this column to the founding date of the company. Let's say the 19th of November, 2004.

6. NULLIF

Another useful null-related function is NULLIF(). It compares two values. If they are equal it returns NULL, if not, the first value is returned. For example, as we saw in a previous example, customers have a billing and mailing address - sometimes they are the same. Say we have accidentally been sending invoices to the mailing address rather than the billing address. That means we only want to resend invoices to customers if their billing and mailing addresses are different. We can use NULLIF() to know which customers need to have their invoices resent and to which city. If the cities are the same, NULL is outputted and no action is needed. Note that the billing city is the first input in this function, meaning that if the cities are different, the billing city is returned.

7. COALESCE

Another key function is COALESCE(). It returns the first non-null value in a list. Customers are asked for contact information from their phone, email, to fax. However, not all customers provide information for each contact method. I mean, how many of you actually own a fax, and if you do, do you still use it? We can use COALESCE() to return the best method for contacting a customer. Calling a customer is ideal because it is direct communication, but if no phone number is provided, email is the next preferable method. And when there is neither a phone number or email, we can fax them. This is what the SQL query would look like to do this. Customer 58 does not have a phone number, but has an email on record.

8. Let's practice!

Can you handle NULL values? Time to find out.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.