MySQL Replace NULL With 0 in SELECT Statement

When you make a Left Join or make a UNION query the DBMS will put NULL where their is no match in the set. This tutorial is aimed to discuss the options by which NULL can be substituted with 0 or any other value you wish to be filled in the MySQL DBMS.

COALESCE Function

This function will return the the first non-NULL value in the its argument list.

SELECT COALESCE(empSecondName, '0') AS employeeSecondName FROM employees; 

IFNULL Function

IFNULL function can also be used to replace NULL values in the result set. It simply check if the value in its first argument is NULL it will replace it with the value of the second argument.

SELECT IFNULL(empSecondName, 'No Name Found') AS employeeSecondName FROM employees;

CASE Statement

CASE statement give more liberty when for replacement

SELECT CASE 
WHEN empSecondName is NULL THEN 'Ask Employee for Second Name'
/*You can add as many WHEN statements as you like*/
ELSE empSecondName END AS employeeSecondName 
FROM employees;