In This Article, You Will Know About SQL NULL Function.
SQL NULL Function – Before moving ahead, let’s know a bit about the SQL CASE Statement.
Table of Contents
SQL NULL Functions
The SQL NULL Functions are used to replace the NULL value with another value. Functions are different and work on their usage.
Example Database
Table name: Data_1
S.No | Name | Roll_No | Section_ID |
1 | Alex | 5 | A |
2 | Rolex | 6 | B |
3 | Salex | 7 | C |
4 | Jalex | ||
5 | Kalex | 9 | E |
IFNULL() : The IFNULL() function used to replace NULL value with another value. This function is not available in SQL or Oracle and is available only in MySQL.
Syntax:
SELECT column(s), IFNULL(column_name, value_to_replace)
FROM table_name;
MySQL
Example: Use IFNULL() function to return an alternative value if an expression is NULL.
SELECT Name, IFNULL(Roll_No, 8) AS Roll_No
FROM Data_1;
As a result, it replaces the Null value in Roll_No as 8.
ISNULL() : The ISNULL() function is available in both server SQL and MySQL but have different use ISNULL() function in both server.
Syntax:
SELECT column(s), ISNULL(column_name, value_to_replace)
FROM table_name;
Example: Use ISNULL() function to return an alternative value if an expression is NULL.
SELECT Name, ISNULL(Section_ID, 'D') AS Section_ID
FROMM Data_1;
MySQL
In MySQL, the ISNULL() function is used to check whether an expression is NULL or not. If the expression is NULL it returns TRUE, else FALSE.
Syntax:
SELECT column(s)
FROM table_name
WHERE ISNULL(column_name);
Example: Use ISNULL() function to return an alternative value if an expression is NULL.
SELECT Name
FROM Data_1
WHERE ISNULL(Section_ID);
As a result, it returns the names of all students who have section_id.
COALESCE() : The COALESCE() function returns the first expression among all not NULL expressions.
If all the expressions are NULL, then it returns NULL.
Syntax:
SELECT column(s), COALESCE(expression_1,....,expression_n)
FROM table_name;
Example: Use COALESCE() function to return the first non-NULL expression.
SELECT Name, COALESCE(Roll_No, Section_ID) as info
FROM Data_1;
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
