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
| 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:
SELECT *
FROM Orders
WHERE CustomerID IS NULL;
This returns
| OrderID | Product | Quantity | CustomerID |
|---------|---------|----------|------------|
| 2 | Banana | 30 | NULL |
| 4 | Mango | 40 | NULL |
2) Replacing NULL
We can replace NULL values using the COALESCE() function:
SELECT OrderID,
Product,
Quantity,
COALESCE(CustomerID, 'Not Provided') as CustomerID
FROM Orders;
This returns
| 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
SELECT *
FROM Orders
WHERE Quantity IS NOT NULL;
This returns
| 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
SELECT OrderID, Product, Quantity + 10 as 'New Quantity', CustomerID
FROM Orders;
This returns
| 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.
