What are the Null functions present in MySQL?
We have two null functions as below in the mysql
1. IFNULL()
2. COALESCE()
We will see the use of each function one by one.
1. IFNULL()
This function will check if the given value is null. if that value is null, the alternate value provided will be returned.
Let’s consider the following ‘users’ table with the values listed below.
From the table data, we can see that for user ‘PCY’ we do not have any number details and that value is null. So when we select this value we will check if that value is null using the IFNULL function and will return ‘NO_DATA’ if null.
The query for the above check will be as below.
SELECT
user_id,
user_name,
user_email,
IFNULL(user_number, ‘NO_DATA’) AS user_number,
birthdate
FROM
users;
Output:
2. COALESCE():
This is also similar to the IFNULL function but we can provide a list of values to be returned if the value is null. This function returns the first Non-null value from the provided list.
Like in the below example if we have a null value in the first second and third place then the fourth place value which is not null will be returned.
SELECT
user_id,
user_name,
user_email,
COALESCE(user_number,null, null, ‘NO_DATA’ ) AS user_number,
birthdate
FROM
users;
This function is useful when we are selecting from multiple tables and need to check if any value is null then consider that value from a different table.
Let’s consider that we have the following three tables for users.
From the above table details, we can see that the users and facebook_users table as the null entries for the ‘PCY’ user’s number, and insta_users have that entry. Now we will see the following query. When we are first selecting from the users’ table, if that value is null then COALESCE function will check for the facebook_users column, if that value is also null then it will check for the next value which is the insta_users column data.
select
u.user_id,
u.user_name,
u.user_email,
COALESCE(u.user_number, f.user_number, i.user_number) AS user_number,
u.birthdate
from users as u,
facebook_users as f,
insta_users as i
where f.user_email = u.user_email
AND i.user_email = u.user_email
order by u.user_id;
Output:
