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.

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.

This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

My Personal Notes arrow_drop_up

Save