In this topic, we described about the IF NULL Function with detailed example.
IF NULL function is used to specify a value other than NULL value in result set. IF NULL works on MY SQL database only. IF NULL consists of two expressions, if first expression contains NULL values it returns second expression. If it does not contain NULL values, it returns first expression itself. IF NULL returns numeric or string value based on the scenario.
IF NULL (expression1, expressio2|value) FROM table;
- expression1 – It represents the name of the expression or column name on which we want to test NULL values.
- expression2 – It represents the name of the expression or column name which we want to insert values on nullable column.
- Value – It represents the value that we need to specify on required column which have the NULL values.
Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.
Scenario – Replacing existing NULL values on table by using IF NULL.
Requirement – Replacing NULL values on existing employee_details table by using IF NULL. The query was as follows –
SELECT emp_id,emp_name,designation,IFNULL(manager_id, 'Employee0') manager_id FROM employee_details;
By executing above query, we can get specified value on manager_id in employee_details table because manager_id consists NULL values. The output was as follows –
Scenario – If we trying to replace NOT NULL column with a specified value by using IFNULL.
Requirement – If we trying to replace NOT NULL column emp_name on employee_details table by using IFNULL.
SELECT emp_id, IFNULL(emp_name, 'Employee1')emp_name FROM employee_details;
By executing above query, we can get existing emp_name column values on employee_details because it won't consists NULL values. The output was as follows –
Scenario – If we trying to combine multiple columns which have NULL values getting without NULL values in result set on existing table by using IFNULL function.
Requirement – If we trying to add emp_id, emp_name, manager_id, designation columns on employee_details table without getting NULL values by using IFNULL function. The query was as follows –
SELECT emp_name + IFNULL(manager_id, ' ') + designation 'NAME WITH DESIGNATION' FROM employee_details;
By executing above query, we can get output without NULL values in result set from employee_details table. The output was as follows –
|NAME WITH DESIGNATION|
Scenario – Replacing NULL values in the existing table with another specified column value in result set by using IFNULL.
Requirement – Replacing NULL values in employee_details with designation value in result set by using IFNULL. The query was as follows –
SELECT emp_id,emp_name,IFNULL(manager_id,designation )manager_id FROM employee_details;
By executing above query we can replace NULL values on manager_id column in existing employee_details table with designation column values. The output was as follows -