MySQL View check if data is NULL

Question

I need to put a Case in the Select to check if the Data I'm adding to my view is NULL, in which case I want it to just enter a zero, or not.

Solution 1

You mean something like this?

SELECT IF(`field` IS NULL, 0, `field`)...

There's also "IFNULL()":

SELECT IFNULL(`field`, 0)...

Solution 2

select coalesce(field, 0) as 'field' from v;

(doc)

Solution 3

When creating your table just add NOT NULL to the column description, e.g.

CREATE TABLE ( ID INT NOT NULL default '0' );

Then if no data is given for the field it is set to the default value of 0 which will be retrieved when you run a SELECT query.

Comments

[Popeye The Sailor]

this is a classic case of misusing IF() and IFNULL() where the more generally accepted COALESCE() is a better idea.

[Popeye The Sailor]

this doesn't apply if, for example, the NULL is the result of a LEFT OUTER JOIN.