In this Blog post, we will show you how to handle null values in MySQL by replacing them with a default value of your choice. Specifically, we will explain how to use the IFNULL() and COALESCE() functions to show a value of 0 when a column’s value is NULL.
Before I can explain it to you properly, let me create a table first.
CREATE TABLE `tbl_student_list` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`fees` int(11) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`mobile_no` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `tbl_student_list`
ADD PRIMARY KEY (`id`);
ALTER TABLE `tbl_student_list`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
Insert some data on table tbl_student_list
INSERT INTO `tbl_student_list` (`id`, `name`, `fees`, `email`, `mobile_no`) VALUES
(1, 'rajesh', 10000, 'rajesh@gmail.com', '9999999999'),
(2, 'savi', 12000, 'savi@gmail.com', '9999999999'),
(3, 'swara', NULL, 'swara@gmail.com', '9999999999'),
(4, 'sanchi', 15000, 'sanchi@gmail.com', '9999999999');
Now table looks like
We will then show you how to use the IFNULL() function to replace null values with a default value of 0 in a SELECT statement.
SELECT name,IFNULL(fees, 0) as fees,email,mobile_no FROM tbl_student_list
Output
Now we use COALESCE() function which is widely used for this purpose as well.
SELECT name,COALESCE(fees, 0) as fees,email,mobile_no FROM tbl_student_list
Output
We will also provide examples and explain the output of the function to help you understand how it works. This post is great for anyone who wants to learn more about handling null values in MySQL