SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF

1. Null Functions in SQL

In this SQL (Structured Query Language) tutorial, we will see SQL Null Functions. In this, we will discuss Types of Null Functions in SQL such as SQL ISNULL, SQL IFNULL, SQL Server NULLIF, SQL NVL, COALESCE SQL. 

So, let us start SQL Null Functions.

SQL Null Functions

SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF

2. Types of SQL Null Functions

Following are the types of NULL Functions in SQL. Let’s discuss them one by one, with the help of example and syntax:

a. SQL ISNULL()

The SQL ISNULL work has distinctive uses in SQL Server and MySQL. In SQL Server, ISNULL() work is used to supplant NULL qualities.
The syntax of SQL ISNULL –

SELECT column(s), ISNULL(column_name, value_to_replace)
FROM table_name;

Example of SQL ISNULL –
Consider the accompanying Employee table,

SQL Null Functions

Example of SQL ISNULL

Do you know about SQL Data Types
Question: Find the whole of pay of all Employee, if Salary of any representative isn’t accessible (or NULL value), use pay as 10000.

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

SQL ISNULL()

In MySQL ISNULL() work is used to test whether a said is NULL or not. In the event that the said is NULL, it returns TRUE, else FALSE.
The syntax of MySQL ISNULL –

SELECT column(s)
FROM table_name
WHERE ISNULL(column_name);

Example of MySQL ISNULL –
Consider the accompanying Employee table,

SQL Null Functions

MySQL ISNULL()

SELECT Name
FROM Employee
WHERE ISNULL(Salary);

Result:

SQL Null Functions

MySQL ISNULL

Have a look at SQL RDBMS Concept

b. SQL IFNULL()

This capacity is accessible in MySQL, and not in SQL Server or Oracle. This capacity takes two contentions. In the event that the primary contention isn’t NULL, the capacity restores the main contention. Something else, the second contention is returned. This capacity is normally used to supplant NULL incentive with another value.
The Syntax of SQL IFNULL–

SELECT column(s), IFNULL(column_name, value_to_replace)
FROM table_name;

Example of SQL IFNULL –
Consider the accompanying Employee table,

SQL Null Functions

SQL Server IFNULL()

Question: Find the entirety of compensation of all Employee, if Salary of any worker isn’t accessible (or NULL value), use pay as 10000.

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

Result:

Join DataFlair on Telegram
SQL Null Functions

SQL IFNULL

Let’s revise SQL Expressions

c. SQL Combine()

COALESCE work in SQL restores the primary non-NULL said among its contentions. On the off chance that every one of the said assesses to null, at that point the COALESCE capacity will return null.
The Syntax of SQL COALESCE

SELECT column(s), CAOLESCE(expression_1,....,expression_n)
FROM table_name;

Example of SQL Combine
Consider the accompanying Contact_info table,

SQL Null Functions

COALESCE SQL

Question: Fetch the name, contact number of every representative.
Do you know about SQL create database

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

Result –

SQL Null Functions

SQL Combine

d. NULLIF()

The NULLIF work takes two contentions. On the off chance that the two contentions are equivalent, at that point, NULL is returned. Generally, the main contention is returned.
The example of SQL Server NULLIF:

SELECT column(s), NULLIF(expression1, expression2)
FROM table_name;

Consider the accompanying Sales table,

SQL Null Functions

SQL Server NULLIF()

SELECT Store, NULLIF(Actual, Goal)
FROM Sales;
SQL Null Functions

SQL Server NULLIF

Let’s learn about Distinct Keywords in SQL
Look at the following “Products” table:

P_IdProductNameUnitPriceUnitsInStockUnitsOnOrder
1Jarlsberg10.451615
2Mascarpone32.5623
3Gorgonzola15.67920

Here that the “UnitsOnOrder” column is optional, and that may contain NULL values.
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products
In the example above, if any of the “UnitsOnOrder” values are NULL, the result is NULL.
Below, if “UnitsOnOrder” is NULL it will not harm the calculation, because ISNULL() returns a zero if the value is NULL:

  • MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+IIF(ISNULL(UnitsOnOrder),0,UnitsOnOrder))
FROM Products
Do you know about SQL RDBMS Database

  • SQL Server

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

  • Oracle

Oracle does not have an ISNULL() function which is why we use the NVL() function to achieve the same result:
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products

  • MySQL

MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft’s ISNULL() function.
In MySQL we can use the IFNULL() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
Have a look at SQL Joins
or we can use the COALESCE() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
So, this was all in SQL Null Functions. Hope you like our explanations.

3. SQL Null Functions – Conclusion

Hence, in this SQL Null Functions tutorial, we learned about different types of NULL Functions in SQL. Moreover, we discussed NULLIF(), Combine(), IFNULL(), ISNULL() with example and syntax. Still, if any doubt regarding SQL Null Functions, ask in the comment tab.
Related Topic-
SQL Null Values
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.