AppDividend
Latest Code Tutorials

SQL NULL Functions | IFNULL(), ISNULL(), COALESCE(), NULLIF

0

SQL NULL Functions represent any value that is missing. SQL NULL is described as a blank in a table under any column. The NULL value is something different from Zero and Blank Spaces. NULL functions are the functions that are used for the replacement of NULL values.

SQL NULL Functions

We will see these IFNULL(), ISNULL(), COALESCE(), NULLIF functions.

Let’s discuss it one by one:

Consider the table: (Employee)

Emp_idEmp_nameCityStateSalaryPhoneAlternate Phone
101RohitPatnaBiharNULL99245125488545125485
201ShivamJalandharPunjab2000095854512549585451254
301KaranAllahabadUttar Pradesh12000NULL8001225845

 

#ISNULL()

The use of the ISNULL() function is used to replace NULL values in SQL Server.

In MySQL, this function is used to test whether an expression is NULL or not. It usually returns Boolean Values.

#Syntax

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

#Parameters

  1. Column_name: Column which contains NULL values.
  2. Value_to_replace: Value to be replaced in that column.
  3. Table_name: Name of the table.

#Example

See the following example.

Select SUM(ISNULL(Salary, 50000) 
AS SALARY from Employee;

#Output

SALARY
820000

 

#Explanation

Here in the above query, we have performed the sum of the salary of employees, and as 1st row had NULL values in the SALARY column his value was replaced by the value as mentioned in the above query, and hence the sum is displayed.

#Syntax: (For MySQL)

See the following syntax.

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

#Parameters

  1. Column_name: Column which contains NULL values.
  2. Table_name: Name of the table.

#Example

See the following example.

SELECT Emp_id, Emp_name, City, State 
FROM Employee 
WHERE ISNULL(Salary);

#Output

Emp_idEmp_nameCityState
101RohitPatnaBihar

 

#Explanation

Here in the above query details of the employee were displayed whose salary was NULL.

#IFNULL()

The IFNULL function is used for replacing the NULL values in MySQL. This function takes two arguments. The function will return the first argument if it is not NULL. Otherwise, the second argument will be returned.

#Syntax

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

#Parameters

  1. Column_name: Column which contains NULL values.
  2. Value_to_replace: Value to be replaced in that column.
  3. Table_name: Name of the table.

#Example

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

#Output

SALARY
820000

 

#Explanation

The IFNULL function works the same as that of ISNULL in SQL server. Refer, to the ISNULL function Example, as shown above.

#COALESCE

This function is used for returning the first non-NULL expression among its arguments.

It will check for all its expressions if all of them evaluates to NULL, then the function will return NULL.

#Syntax

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

#Parameters

  1. Column(s): Column Names.
  2. expression_1…., expression_n: Expressions which is used to return values.
  3. Table_name: Name of the table.

#Example

SELECT Emp_name AS Name, COALESCE (Phone, Alternate Phone) AS Contact_Number 
FROM Employee;

#Output

NameContact_Number
Rohit8545125485
Shivam9885658442
Karan8001225845

 

#Explanation

Here in the above query, the name and contact_number of each employee are displayed.

Here you can very well see that in the third row of Employee table Phone column was having a NULL value.

So, when COALESCE function is used to display contact_number, its second argument didn’t return a NULL value. So, that value was displayed. Usually, this function displays the value of its last argument.

If the first argument doesn’t return NULL, but the second argument does, then the first argument value will be displayed. If both have been NULL, then NULL will be displayed as said above.

#NULLIF()

This function is used to return NULL if the arguments present inside the function return the same value. Usually, it contains two arguments. If both the arguments are not equal, then the first argument value will be displayed.

#Syntax

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

#PARAMETERS:

  1. Column(s): Column Names.
  2. expression1, expression2: Expressions which is used to return values.
  3. Table_name: Name of the table.

#EXAMPLE

SELECT Emp_Name AS Name, NULLIF (Phone, Alternate Phone) AS Contact_Number 
FROM Employee;

#Output

NameContact_Number
Rohit9924512548
ShivamNULL
KaranNULL

 

#Explanation

Here in the above query, you can very well see that the second row got NULL value because in employee table phone column and alternate phone column had the same value, and in first, third-row phone column value was returned as it was declared in the first argument.

Finally, SQL NULL Functions Example tutorial is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.