Mysql. How to replace NULL with certain, predefined value in resultset without COALESCE

Question

I have such type of query:

SELECT table1.field1, table1.field2, table2.field3
FROM table1
LEFT JOIN table2 ON (...)

because of left join i sometimes have table2.field3 NULL

After that in my php script in cycle i redefine it manually

...
$row['field3'] = ($row['field3'] === null) ? 'undefined' : $row['field3']; 
...

But how can i get this 'undefined' in my result-set?

I ve searched for answer, COALESCE seems to work, but it have some strange logic with many-many arguments. Seems like it do what i want, but somehow strange..

Im interested if there another simple way to replace my NULL with 'undefined'?

COALESCE in mysql

Ullas
– Ullas

2015-08-17 04:39:39 +00:00
Commented Aug 17, 2015 at 4:39 — Ullas
– Ullas, Commented Aug 17, 2015 at 4:39
have you looked at IFNULL ?

Tim Mickey
– Tim Mickey

2015-08-17 04:41:13 +00:00
Commented Aug 17, 2015 at 4:41 — Tim Mickey
– Tim Mickey, Commented Aug 17, 2015 at 4:41
tim Mickey, nope. thanks

Piro Man
– Piro Man

2015-08-17 04:44:18 +00:00
Commented Aug 17, 2015 at 4:44 — Piro Man
– Piro Man, Commented Aug 17, 2015 at 4:44

M0rtiis · Accepted Answer · 2015-08-17 04:48:23Z

3

You should not be afraid of COALESCE... its ok.

But, there are other ways:

Just use IF & IS NULL

SELECT table1.field1, table1.field2, IF(table2.field3 IS NULL, 'undefined', table2.field3) AS field3
Or simpler, use IFNULL (i believe you will like it)

SELECT table1.field1, table1.field2, IFNULL(table2.field3, 'undefined') AS field3

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

edited Aug 17, 2015 at 4:48

answered Aug 17, 2015 at 4:39

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emipro Technologies Pvt. Ltd. · Accepted Answer · 2015-08-17 05:09:34Z

1

You can also use case when,

Select 
    case when table1.field1 is null then 
        case when table1.field2 is null then 'undefined'
        else table1.field2 end 
    else table1.field1 end as field1
from tablename

you can use nested case when in case you want to check further level conditions.

answered Aug 17, 2015 at 5:09

Emipro Technologies Pvt. Ltd.

14.8k5 gold badges48 silver badges76 bronze badges

Collectives™ on Stack Overflow

Mysql. How to replace NULL with certain, predefined value in resultset without COALESCE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related