Understanding SQL NULL functions, explaining their syntax and illustrating their use with practical examples.
SQL is a standard language for managing data held in a relational database management system. Among the vast number of functions and capabilities provided by SQL, NULL functions are a crucial feature that every SQL user should understand.
What is NULL in SQL?
In SQL, NULL signifies that a particular field has no value or is essentially missing. It’s important to understand that NULL doesn’t mean zero or blank, it means no entry was made or an explicit NULL was given. This can happen when the value is unknown or not applicable.
SQL Null Functions
SQL NULL functions are used to handle NULL values in a database. The most common NULL functions are ISNULL(), COALESCE(), and NULLIF().
1. ISNULL():
The ISNULL() function is used to replace NULL values with a specified value.
The syntax of the ISNULL() function is as follows:
ISNULL(expression, value_if_null)
expression: the field or value to check for NULL.-
value_if_null: the value to return if the expression is NULL.
For example, let’s consider a table, Employees, where some entries in the Salary column might be NULL:
| ID | Name | Salary |
|----|--------|--------|
| 1 | John | 5000 |
| 2 | James | NULL |
| 3 | Julia | 6000 |
The following SQL statement replaces NULL values in the “Salary” column with “0”:
SELECT Name, ISNULL(Salary, 0) AS Salary
FROM Employees;
The result is:
| Name | Salary |
|-------|--------|
| John | 5000 |
| James | 0 |
| Julia | 6000 |
2. COALESCE():
COALESCE() function is used to return the first non-null value in a list. If all arguments are NULL, COALESCE returns NULL.
Here is the basic syntax of COALESCE():
COALESCE(expression1, expression2, ..., expression_n)
For example, using the same Employees table, if you have another column “Bonus” which might also contain NULL values:
| ID | Name | Salary | Bonus |
|----|--------|--------|-------|
| 1 | John | 5000 | NULL |
| 2 | James | NULL | 1000 |
| 3 | Julia | 6000 | NULL |
The following SQL statement returns the first non-null value from the “Salary” or “Bonus” column:
SELECT Name, COALESCE(Salary, Bonus) AS Earnings
FROM Employees;
The result is:
| Name | Earnings |
|-------|----------|
| John | 5000 |
| James | 1000 |
| Julia | 6000 |
3. NULLIF():
NULLIF() function returns NULL if two expressions are equal. If the expressions are not equal, the first expression is returned.
The syntax of NULLIF() is as follows:
NULLIF(expression1, expression2)
Suppose you have a table Products with “Price” and “Discounted_Price” columns:
| ID | Product | Price | Discounted_Price |
|----|---------|-------|------------------|
| 1 | A | 20 | 15 |
| 2 | B | 30 | 30 |
| 3 | C | 40 | 35 |
The following SQL statement returns NULL if the “Price” and “Discounted_Price” are the same, otherwise, it returns “Price”:
SELECT Product, NULLIF(Price, Discounted_Price) AS Effective_Price
FROM Products;
The result is:
| Product | Effective_Price |
|---------|-----------------|
| A | 20 |
| B | NULL |
| C | 40 |
This means product B is not on discount as the original price and the discounted price are the same.
Conclusion
SQL NULL functions offer powerful ways to handle NULL values and provide meaningful data interpretation, which is crucial for sound data analysis.
