SQL NULL

In this tutorial, we will learn how to use IFNULL(), ISNULL(), COALESCE(), and NVL() Functions.

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

All the Functions returns the specified value if the value in the specified column inside these function is NULL. In other words, All these functions are used to check the value is NULL or not, if the values are NULL, then specified value is return.

Syntax of IFNULL Function

IFNULL function is used in MySQL.

IFNULL(Expression, alternate_value);

Syntax of ISNULL Function

In MSSQL, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse, ISNULL function is used.

ISNULL(Expression, alternate_value);

Syntax of ISNULL Function used in MySQL

ISNULL(Expression);

MySQL COALESCE Function

MySQL COALESCE Function return the first non-NULL value of a list or return NULL if there are no non-NULL values in the list.

Syntax of MySQL COALESCE function

COALESCE(value1,value2,value3,...valueN)

Oracle NVL Function

NVL function is very similar to MySQL IFNULL function and SQL Server ISNULL. NVL function is used to replace NULL value to a specified value.

Syntax of NVL Function used in Oracle

NVL(Expression,alternate_value);

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

Example of MySQL IFNULL Function

For selecting records if City and Country is NULL, then it returns "n/a", otherwise it returns City and Country using IFNULL function, the query will be:

SELECT ID,EmpName,IFNULL(City,'n/a'),IFNULL(Country,'n/a')
FROM Employee;

Example of SQL Server ISNULL Function

For selecting records if City and Country are NULL, then it returns "n/a", otherwise it returns City and Country using ISNULL function, the query will be:

SELECT ID,EmpName,ISNULL(City,'n/a'),ISNULL(Country,'n/a')
FROM Employee;

Example of MySQL ISNULL Function

For selecting records if City is NULL, then it returns '0', otherwise it returns '0' and Country using MySQL ISNULL function, the query will be:

SELECT ISNULL(City)
FROM Employee;

Example of MySQL COALESCE Function

For selecting records if City and Country are NULL, then it returns "n/a", otherwise it returns City and Country using COALESCE function, the query will be:

SELECT ID,EmpName,COALESCE(City,'n/a'),COALESCE(Country,'n/a')
FROM Employee;

Example of Oracle NVLFunction

For selecting records if City and Country are NULL, then it returns "n/a", otherwise it returns City and Country using NVL function, the query will be:

SELECT ID,EmpName,NVL(City,'n/a'),NVL(Country,'n/a')
FROM Employee;