SQL IFNULL on SELECT * statement

Question

Is there a way to set a default value for all returned values that are null, specifically in MySql?

Instead of

SELECT 
  IFNULL(foo, 0),
  IFNULL(bar, 0),
  IFNULL(baz, 0)
FROM table

I would like to do something like:

SELECT IFNULL(*, 0)
FROM table

// this does not work, but I'm looking for something like it

Thanks for the feedback, and for anyone else that comes upon this post, the reason I cannot place an IFNULL on each item is due to the fact that I'm dynamically generating the SQL in an ORM style, so I know neither the number of columns, or data types of those columns being returned, but I then LEFT JOIN against another table (a table of days, for example) and do a select * from the dynamic table. — sabrams, Jun 11 '16 at 01:44

score 4 · Accepted Answer · answered Jun 09 '16 at 22:28

4

A result of a function in SQL is a single value (column), not a list of columns. So there isn't, and can't be, such a construct. You'd have to handle every column individually, unfortunately.

answered Jun 09 '16 at 22:28

Mureinik

297,002
52
306
350

3

Not the answer I was hoping for, but certainly good to know for sure. – sabrams Jun 11 '16 at 01:40

Hart CO · Answer 2 · 2016-06-09T22:34:05.637

0

You could add default values to the table if you don't care about preserving NULL:

ALTER TABLE yourTable ALTER foo SET DEFAULT 0;

You could also create a view where you write out each of the IFNULL() statements once and just use that to save from re-typing it all the time.

But as others indicated, no construct to do it simply like you hoped.

You can also quickly do repetitive things like this using table metadata:

SELECT CONCAT("IFNULL(",`COLUMN_NAME`,",0)")
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

edited Jun 09 '16 at 22:34

answered Jun 09 '16 at 22:28

Hart CO

34,064
6
48
63

1

Unfortunately many of the values in my select are calculated values, and there are some tables where I do want nulls in the table, but just not in the report being generated. – sabrams Jun 11 '16 at 01:42

score -1 · Answer 3 · answered Jun 09 '16 at 22:32

-1

You can try case statement for sql as below

    select 
CASE 
  WHEN foo is null THEN 0  else foo end

  from urtable

and then same for other columns

answered Jun 09 '16 at 22:32

Atul Chaudhary

3,698
1
31
51

SQL IFNULL on SELECT * statement

3 Answers3