AppDividend
Latest Code Tutorials

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

0

SQL NULL Functions Example | NULL Functions in SQL Tutorial is today’s topic. The SQL Term NULL represents any value that is missing. It is described as a blank in a table under any column. One should remember that a NULL value is something different from Zero and Blank Spaces. NULL functions are the functions which are used for 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_id Emp_name City State Salary Phone Alternate Phone
101 Rohit Patna Bihar NULL 9924512548 8545125485
201 Shivam Jalandhar Punjab 20000 9585451254 9585451254
301 Karan Allahabad Uttar Pradesh 12000 NULL 8001225845

 

#ISNULL()

The use of 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 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_id Emp_name City State
101 Rohit Patna Bihar

 

#Explanation

Here in the above query details of the employee was 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

Name Contact_Number
Rohit 8545125485
Shivam 9885658442
Karan 8001225845

 

#Explanation

Here in the above query, the name and contact_number of each employee is 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

Name Contact_Number
Rohit 9924512548
Shivam NULL
Karan NULL

 

#Explanation

Here in the above query, you can very well see that 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 | IFNULL(), ISNULL(), COALESCE(), NULLIF 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.