MySQL IFNULL ELSE

Question

I have a select statement where I want to make the select conditional like this:

IFNULL(field_a, field_a, field_b)

so that it checks field a. If a is null then the select would be field b.

Is that possible ?

Solution 1

Use COALESCE:

SELECT COALESCE(field_a, field_b)

COALESCE is an ANSI standard function that returns the first non-null value from the list of columns specified, processing the columns from left to right. So in the example, if field_a is null, field_b value will be displayed. However, this function will return NULL if there is no non-null value from the columns specified.

It's supported on MySQL (I've used it on 4.1), SQL Server (since v2000), Oracle 9i+...

Solution 2

and another way to skin that cat (flexible for not just null comparisons)...

select if(field_a is not null, field_a, field_b) from...

Solution 3

Yes, but it's just two parameters:

IFNULL(field_a,field_b)

If field_a is not null, it will be returned. Otherwise field_b will be returned.

Reference: IFNULL

Comments

[Chuck Callebs]

I support the use of COALESCE as well.

[Apolo]

Best answer. Allow to do Select if(name is not null, CONCAT('name is : ',name),'');

[Andrew]

Such a clear explanation of how the function works... Thanks! Updated the broken link.

[Arda]

select IFNULL(column, 'default') also works. See here: sqlfiddle.com/#!9/14b752/1/0

[TommyAutoMagically]

Pretty cool explanation of a function I'm not familiar with! However, for OP's question, using IF() is much more readable.