SQL | NULL functions

In the database, null values serve as placeholders for data that is either missing or not available. a null value is a flexible data type that can be placed in the column of any data type, including string, int, blob, and CLOB datatypes. It is not a component of any specific data type. Null values are helpful when cleaning the data prior to exploratory analysis.

Null values assist us in eradicating data ambiguity. Null values are also useful for maintaining a consistent datatype across the column. We will learn about the necessity and guidelines for using Null values in this article. Now let’s use examples to try to better understand null values and null functions in SQL.

Some key points regarding the use of NULL in SQL:

1. Comparison with NULL: Comparisons with NULL using regular comparison operators like “=”, “<>”, “<“, “>” do not yield true or false but rather produce a result of unknown or NULL. Instead, you need to use the IS NULL or IS NOT NULL operators to check for NULL values. 
2. Handling NULL in expressions: When performing arithmetic or other operations involving NULL values, the result typically becomes NULL. For example, any arithmetic operation that involves a NULL operand will result in a NULL result.
3. Aggregating NULL values: Most aggregate functions in SQL, such as SUM, AVG, COUNT, etc., ignore NULL values when calculating results. However, there are some aggregate functions like COUNT(*) that consider NULL values. 
4. Indexing and NULL: Some database systems handle NULL values differently when it comes to indexing. 
5. It’s important to consult the specific documentation of your database management system to understand how NULL values are treated in index structures. Potential pitfalls: The presence of NULL values can introduce complexity and potential pitfalls when querying or manipulating data. It requires careful consideration to handle NULL values appropriately in SQL queries to avoid unexpected or incorrect results.

Why do We Need NULL Values?

Null functions are required to perform operations on null values ??stored in the database. With NULL values, we can perform operations that clearly identify whether the value is null or not. With this ability to recognize null data,  operations similar to SQL’s join methods can be performed on them.

Following are the NULL functions defined in SQL:

ISNULL()

The ISNULL function has different uses in SQL Server and MySQL. In SQL Server, ISNULL() function is used to replace NULL values. 

Syntax:

SELECT column(s), ISNULL(column_name, value_to_replace)

FROM table_name;

Example: Consider the following Employee table,

Find the sum of the salary of all Employees, if the Salary of any employee is not available (or NULL value), use salary as 10000.

Query:

SELECT SUM(ISNULL(Salary, 10000) AS Salary
FROM Employee;

Output: 

In MySQL, ISNULL() function is used to test whether an expression is NULL or not. If the expression is NULL it returns TRUE, else FALSE. 

Syntax:

SELECT column(s)

FROM table_name

WHERE ISNULL(column_name);

Example: Consider the following Employee table

Fetch the name of all employees whose salary is available in the table (not NULL).

Query:

SELECT Name
FROM Employee
WHERE ISNULL(Salary);

Output: 

IFNULL()

This function is available in MySQL, and not in SQL Server or Oracle. This function take two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace NULL value with another value. 

Syntax:

SELECT column(s), IFNULL(column_name, value_to_replace)

FROM table_name;

Example: Consider the following Employee table,

 Find the sum of the salary of all Employees, if the Salary of any employee is not available (or NULL value), use salary as 10000.

Query;

SELECT SUM(IFNULL(Salary, 10000) AS Salary
FROM Employee;

Output:

COALESCE()

COALESCE function in SQL returns the first non-NULL expression among its arguments. If all the expressions evaluate to null, then the COALESCE function will return null. 

Syntax:

SELECT column(s), CAOLESCE(expression_1,….,expression_n)

FROM table_name;

Example: 

Consider the following Contact_info table, 

Fetch the name and contact number of each employee.

Query:

SELECT Name, COALESCE(Phone1, Phone2) AS Contact
FROM Contact_info;

Output: 

NULLIF()

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. 

Syntax:

SELECT column(s), NULLIF(expression1, expression2)

FROM table_name;

Example: Consider the following Sales table

SELECT Store, NULLIF(Actual, Goal)
FROM Sales;

Output: 

Conclusion

In this article, we learned what null values are and why we should use them. We now know that using NULL values is fundamental to databases and is done so in order to preserve their integrity. Following this, we learned more about the different functions that can be used with NULL values. In conclusion, the NULL value in SQL represents the absence or unknown value for a particular data field. It requires special consideration when writing queries and expressions to handle NULL values properly and avoid potential pitfalls.

Frequently Asked Question

Q1: How can I handle NULL values in SQL queries?

Answer:

You can handle NULL values in SQL queries using various techniques. For example, you can use the IS NULL and IS NOT NULL operators to filter rows based on the presence or absence of NULL values. Additionally, you can use functions like COALESCE or ISNULL to substitute NULL values with a specified default value.

Q2: Can I insert a NULL value into a column with a NOT NULL constraint?

Answer: 

No, a column with a NOT NULL constraint must have a non-NULL value. Attempting to insert a NULL value into such a column will result in an error.