Skip to content

Dealing With NULL Values in SQL

In SQL, NULL is a special marker used to indicate that a data value in a database table does not exist in the database. It represents the absence of a value or an unknown value.

There are many ways to build SQL competency so we have decided to take a different approach. For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu. 

Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.

HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.

YOU’VE GOT MAIL!

Good Morning!
I was talking with the commercial team to understand the average sales per restaurant for 2023 but they complained about some missing date data. Can you take a look and get the value despite this
?
Best Regards,
Mrs Ahmadu (CGO)

SQL TOPIC OF THE DAY 

A solid understanding of SQL NULL empowers you to manage data more effectively, experiment with different use cases to enhance your skills and ensure robust data handling practices in your SQL endeavours. Unlike other data types that may have specific values, NULL is not equal to any other value, including itself. Therefore, NULL is not the same as an empty string, zero, or any other “known” value.

Common Use Cases for the CASE statement:

1. Marking Unknown or Unavailable Data

In scenarios where the exact data is not available or is unknown, NULL serves as a suitable placeholder. Consider a products table with a column for expiry_date. For products with no expiration date specified, you can use NULL to indicate the unknown status:

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50),
    expiry_date DATE
);

INSERT INTO products (product_id, product_name, expiry_date)
VALUES
    (1, ‘Product A’, ‘2023-03-15’),
    (2, ‘Product B’, NULL),
    (3, ‘Product C’, ‘2023-05-22’);
Here, Product B’s expiration date is not known, so the `expiry_date` column is assigned NULL.

2. Creating Conditional Queries

NULL values can be handled in queries using the IS NULL or IS NOT NULL conditions. Suppose you want to retrieve a list of students who have not provided their date of birth:

SELECT student_id, student_name
FROM students
WHERE date_of_birth IS NULL;
This query retrieves the student ID and name for those students whose date of birth is not available.

3. Calculating using Conditional Operations

NULL values can be used in conditional operations to handle cases where data may be missing. Suppose you want to calculate the average age of students based on their date of birth. You can use the COALESCE function to replace NULL values with a default date and then perform the calculation:

SELECT AVG(YEAR(CURRENT_DATE) 
          – YEAR(COALESCE(date_of_birth, CURRENT_DATE))) as avg_age
FROM students;
This query calculates the average age of students, considering the current date if the date of birth is missing

SQL PRACTICE 

Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to get the average sales per restaurant for 2023 despite the missing date data.

SELECT r.name, AVG(o.price_paid) as avg_sales,
FROM restaurants r
LEFT JOIN orders o ON r.id = o.restaurant_id
WHERE order_date IS NOT NULL
GROUP BY 1;

FEEDBACK FROM THE CGO

Okay, This looks interesting…
How about the average orders? Kindly provide this for the countries with available dates and for all countries even if the order dates are missing.

Warm regards,
Mrs Ahmadu (CGO

With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!

Loading spinner

Previous Posts