28

I'm selecting the max of a column from a table. But there is one problem: if there are no rows in the table, it returns null.

I want to use a function which will return a certain value if the result is null. For example with Oracle there is the NVL function which gives a certain value if the column is null. Is there an equivalent function in MySQL ?

codeforester
  • 39,467
  • 16
  • 112
  • 140

2 Answers2

52

Use coalesce:

select coalesce(column_name, 'NULL VALUE') from the_table
codeforester
  • 39,467
  • 16
  • 112
  • 140
Thilo
  • 257,207
  • 101
  • 511
  • 656
  • 4
    That is the SQL standard version of NVL, and works in Oracle, too (since 9i, I believe). – Thilo Aug 30 '11 at 06:36
  • 2
    The question asks _"But there is one problem: if there are no rows in the table, it returns null."_ However, coalesce doesn't solve this - if no rows are returned, then no rows are returned. All it does is ensure a non-null value for a certain column *given some rows are returned* But then, neither does nvl in oracle :) – Adam May 22 '16 at 21:48
  • 1
    @Adam. The aggregate function `max` will return a single row (with null in it) even if there are no rows in the table. All the coalesce does (and all the question asked about) is change that null to a default value. – Thilo May 23 '16 at 03:27
0

or you can use IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

select IFNULL(column_name, 'NULL VALUE') from the_table;

taken from: https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#function_ifnull

Egor Plehanov
  • 1