MySQL IFNULL ELSE

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 ?

1

4 Answers

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+...

3

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...

2

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

0

For more examples

MySQL IFNULL() With Syntax & Examples

Syntax

The syntax of the IFNULL function is:

IFNULL(expression_1,expression_2);

examples

 SELECT name, IFNULL(businessphone, homephone) phone FROM users; #The above query return this result from users table +------------+-----------+----------+----------- | id | name | phone | +------------+-----------+----------+----------- | 5 | Tommy Hill | (541) 754-3009 | | 6 | John Smith | (541) 754-3110 | | 10 | Mark Greenspan | (541) 754-3111 | | 11 | Kelvin Row | (541) 754-3111 | +------------+-----------+----------+-----------

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy