SQL Null Values – Understanding SQL Null Values with Detailed Examples

Written by Jagdeesh | 2 min read

Let’s explore an important, yet often misunderstood concept in SQL: NULL values. We’ll also include hands-on examples with a sample dataset.

What is Null in SQL?

NULL in SQL represents missing or unknown values. It is not zero, not an empty string, or not any other default value. It’s simply a representation of ‘no data’. It’s important to understand that a NULL value isn’t equivalent to anything else, not even another NULL value.

When Do We Encounter Null Values?

In a database, you can encounter NULL values under various circumstances. Here are some examples:

1) When we create a new row, but do not provide values for all the columns.
2) When we explicitly insert NULL into a column.
3) During operations that do not yield a result, such as a division by zero.

Sample Data

Consider the following Orders table

output

| OrderID | Product | Quantity | CustomerID |
|---------|---------|----------|------------|
| 1       | Apple   | 50       | 1001       |
| 2       | Banana  | 30       | NULL       |
| 3       | Orange  | NULL     | 1003       |
| 4       | Mango   | 40       | NULL       |
| 5       | Kiwi    | NULL     | 1005       |

SQL Operations with NULL Values

1) Detecting NULL

To find rows where the CustomerID is NULL, we can use the IS NULL clause:

sql

SELECT *
FROM Orders
WHERE CustomerID IS NULL;

This returns

output

| OrderID | Product | Quantity | CustomerID |
|---------|---------|----------|------------|
| 2       | Banana  | 30       | NULL       |
| 4       | Mango   | 40       | NULL       |

2) Replacing NULL

We can replace NULL values using the COALESCE() function:

sql

SELECT OrderID, 
       Product, 
       Quantity, 
        COALESCE(CustomerID, 'Not Provided') as CustomerID
FROM Orders;

This returns

output

| OrderID | Product | Quantity | CustomerID   |
|---------|---------|----------|--------------|
| 1       | Apple   | 50       | 1001         |
| 2       | Banana  | 30       | Not Provided |
| 3       | Orange  | NULL     | 1003         |
| 4       | Mango   | 40       | Not Provided |
| 5       | Kiwi    | NULL     | 1005         |

3) Filtering out NULL Values

We can filter out NULL values using the IS NOT NULL clause

sql

SELECT *
FROM Orders
WHERE Quantity IS NOT NULL;

This returns

output

| OrderID | Product | Quantity | CustomerID |
|---------|---------|----------|------------|
| 1       | Apple   | 50       | 1001       |
| 2       | Banana  | 30       | NULL       |
| 4       | Mango   | 40       | NULL       |

4) Using NULL in Arithmetic Operations

Arithmetic operations involving NULL always return NULL. For instance, if we try to add 10 to each Quantity

sql

SELECT OrderID, Product, Quantity + 10 as 'New Quantity', CustomerID
FROM Orders;

This returns

output

| OrderID | Product | New Quantity | CustomerID |
|---------|---------|--------------|------------|
| 1       | Apple   | 60           | 1001       |
| 2       | Banana  | 40           | NULL       |
| 3       | Orange  | NULL         | 1003       |
| 4       | Mango   | 50           | NULL       |
| 5       | Kiwi    | NULL         | 1005       |

New Quantity is NULL where Quantity was NULL, despite our attempt to add 10.

Conclusion

Understanding and handling NULL values is crucial when working with SQL. They’re instrumental for making accurate analyses, leading to cleaner, more precise data. As always, practicing these commands in real-world scenarios is key to mastering their usage.

Free Course

Master Core Python — Your First Step into AI/ML

Build a strong Python foundation with hands-on exercises designed for aspiring Data Scientists and AI/ML Engineers.

Start Free Course →

Trusted by 50,000+ learners