SQL Null Values – Understanding SQL Null Values with Detailed Examples
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
Related Course
Master SQL — Hands-On
Join 5,000+ students at edu.machinelearningplus.com
Explore Course
Related Articles
