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 ?

Maybe U mixed IFNULL and NULLIF. U need IFNULL: "If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2." — Zvezdochka, Aug 13 '20 at 04:33

score 110 · Accepted Answer · edited Oct 20 '15 at 20:37

110

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

edited Oct 20 '15 at 20:37

Andrew

18,680
13
103
118

answered Jul 09 '10 at 18:23

OMG Ponies

325,700
82
523
502

5

I support the use of COALESCE as well. – Chuck Callebs Jul 09 '10 at 18:40
2

Such a clear explanation of how the function works... Thanks! Updated the broken link. – Andrew Oct 20 '15 at 20:37
Pretty cool explanation of a function I'm not familiar with! However, for OP's question, [using `IF()` is much more readable](https://stackoverflow.com/a/23395407/114558). – rinogo Aug 26 '20 at 20:15

score 86 · Answer 2 · edited Apr 30 '14 at 18:43

86

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

edited Apr 30 '14 at 18:43

Zong

6,160
5
32
46

answered Apr 30 '14 at 18:26

user3590489

873
6
5

3

Best answer. Allow to do `Select if(name is not null, CONCAT('name is : ',name),'');` – Apolo Nov 04 '14 at 14:06
`select IFNULL(column, 'default')` also works. See here: http://sqlfiddle.com/#!9/14b752/1/0 – Arda Dec 17 '15 at 10:48

score 45 · Answer 3 · answered Jul 09 '10 at 18:24

45

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

answered Jul 09 '10 at 18:24

Matt

43,482
6
101
102

norm graham · Answer 4 · 2023-07-03T19:23:56.977

The question was modified, and it appears some answers are not in sync with the current question. Lets start over.
there is no IFNULL(test, NullCase, notNullCase), but there is a switch statement with else. In this example, i did a left join to find if there is a block, so, but I had the same question, because I want a 0 or 1. here is my solution.

select
CASE
WHEN person.blocked is NULL THEN 0
ELSE 1
END AS isBlocked
from mytable

MySQL IFNULL ELSE

4 Answers4

Linked