Returning null or empty string in mysql statement

Question

A table I need to run a query on has fields that can have a NULL value, an empty string, or some text, number, etc.

So for instance I have a table "data" with fields id, field1, field2, field3, field4.

The id is the auto-incremented key, so that will have a value, but the other three fields can have any of the above-mentioned instances.

So the result of of a query on the table could be id=1, field1=NULL, field2='', field3='some text', field4='5'.

How, in the SELECT statement can I return an empty string for each field that is NULL?

Thanks!

[Additional info]

I forgot to mention this until after I already got some replies, so I'm not sure if it'll change the answer.

Also, some of the fields in the table are an ID from another table, so I'm doing inner joins, but there may or may not be a value in an id field in the "data" table.

Using the "data" table from above, if field4 were 5 (and not '5' as originally defined above) and was an id for the table "name", I'd have a join that looked something like:

INNER JOIN name n ON n.id = data.field4

I believe it is the joins that are preventing some rows from returning in my result set. How do I handle that?

Thanks and sorry if anyone already answered.

Answer 1

LEFT JOIN - that's the answer.

I just changed my INNER JOINS to LEFT JOINS and now I'm getting all of the rows, including the ones where the id fields are empty.

Of course, the info I provided before the "[Additional info]" in my original question wasn't the whole story. Sorry, guys, my bad. Salil answered correctly based on the info I originally provided.

Answer 2

Use IFNULL

SELECT id, IFNULL(field1,''), IFNULL(field2,''), IFNULL(field3,'')
FROM data

Answer 3

SELECT id, COALESCE(field1,''), COALESCE(field2,''), COALESCE(field3,'')
FROM   data