MySQL NULL Functions

In mysql, there are 2 functions to handle the NULL values and replace them with some default values. They are

  • IFNULL() function
  • COALESCE() function

IFNULL() Function

The MySQL IFNULL() function, return an alternative value if an expression is NULL.

Consider the following employees table

empno name location office_no mobile_no
001 Andrew India 9898989898
002 Beslin India 9797979797
003 Joanna USA 5012323232
004 Rayan Canada 5125125121

Example query for IFNULL() function

SELECT name,location, IFNULL(office_no, mobile_no) AS contact_number FROM employees;


name location contact_number
Andrew India 9898989898
Beslin India 9797979797
Joanna USA 5012323232
Rayan Canada 5125125121

COALESCE() function

The COALESCE() function is similar to IFNULL function to handle null values.

Example query for COALESCE() function

SELECT name,location, COALESCE(office_no, mobile_no) AS contact_number FROM employees;

Most Read