SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
Interactive Online Courses: Elevate Skills & Succeed Enroll Now!
In this tutorial, we will understand the need and requirements of using the Null values. Let us now try to understand more about null values and null functions in SQL with examples.
What are NULL Values in SQL?
Null values are the placeholders in the database when we have the data missing, or the required data is not available.
A null value is not a part of any particular data type, it is a flexible data type and can be put in the column of any data type be it string, int, blob or CLOB datatype.
Null values come in handy while the cleaning of data during the exploratory analysis of data.
Null values help us in removing the ambiguity arising in data. Also, null values are beneficial to maintain uniform datatype across the column.
Example: Imagine if the user by mistake enters the date of birth in the column of mobile number then ambiguity could arise when contact needs to be established.
To overcome this we run a check on data before insertion and update any data which is not of date datatype with the null value.
Why do we need NULL Functions?
Null functions are required to perform operations on the null values stored in our database. We can perform functions on NULL values, which explicitly recognize if a value is null or not.
Using this recognizing capacity, one can further perform operations on the null values like the aggregate functions in SQL. Some of the functions are as follows:
Sr.No | Function | Description |
1 | ISNULL() | Helps us to replace NULL values with the desired value. |
2 | IFNULL() | Allows us to return the first value if the value is NULL, and otherwise returns the second value. |
3 | COALESCE() | Helps us to return the first non-null values in the arguments. |
4 | NVL() | Helps to replace the NULL value with the desired value given by the user. |
Demo Database
Let us now have a look at our demo database – ‘DataFlair’
Query:Â
SELECT * FROM DataFlair;
Output:
Examples
1. Let us see the use of the IFNULL() function.
Syntax:
SELECT col1,col2, IFNULL(col3, value_to_be_replaced) FROM tableName;
Example: Let us view the experience of each employee in DataFlair and replace the NULL value with 0 years of experience.
Query:
SELECT emp_id,name, IFNULL(experience, 0) FROM DataFlair;
Output:
Here we can see all the values corresponding to NULL are automatically replaced by 0.
2. Let us see the use of ISNULL() function.
Syntax:
SELECT col1,col2, ISNULL(col3) FROM tableName;
Example: Let us now check if the experience of each employee in DataFlair is NULL or not NULL.
Query:
SELECT emp_id,name, ISNULL(experience) FROM DataFlair;
Output:
Here, we can see that the NULL values are converted to 0 and non NULL values are converted to 1.
3. Let us see the use of COALESCE() function.
Syntax:
SELECT col1,col2, COALESCE(col3, value_to_be_replaced) FROM tableName;
Example: Let us view the experience of each employee in DataFlair and replace the NULL value with 0 years of experience using the COALESCE() function.
Query:
SELECT emp_id,name, COALESCE(experience, 0) FROM DataFlair;
Output:
Here, the NULL values are converted to 0 as we have asked the function to convert NULL values to 0.
4. Let us see the use of NVL() function.
Syntax:
SELECT col1,col2, NVL(col3) FROM tableName;
Example: Let us now check if the experience of each employee in DataFlair is NULL or not NULL using the NVL function.
Query:
SELECT emp_id,name, NVL(experience) FROM DataFlair;
Output:
Here, we can see that the non-null values are represented as 1 and the null values are represented by 0.
Summary
In this tutorial, we have seen what null values are and why do we need to use NULL values.
We have seen that the use of NULL values is elementary to the database and is done to maintain the integrity of the database. After this, we have further learned about the various functions that can be run on NULL values.
Some of the major functions like IFNULL(), ISNULL(), COALESCE() and NVL() which operate on NULL values are also discussed.
We have understood the usage and the syntax with the help of examples for each function.
Did you like this article? If Yes, please give DataFlair 5 Stars on Google