CODEWITHSUNDEEP
mysqlconditional-operator
user1086355
user1086355

Reputation: 1333

How to implement ternary conditional operator in MySQL

I want to implement ternary conditional operator in MySQL. I have a table in which one field id exist. Its value may be null. I want to display id in ternary conditional format like this:

select id = id == null ? 0 : id;

Is it possible in MySQL?

Upvotes: 120

Views: 64737

Answers (3)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385274

The documentation is your friend; you should read it!

It says:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

And then lots of examples. This is equivalent to using a ternary conditional with a comparison to NULL and the comparison subject as the second operand; that it doesn't happen to use the symbols ? and : to get you there is not really relevant to anything.

So, in your case:

SELECT IFNULL(`id`, 0) FROM `table`

If you're desperate to provide three operands explicitly (why?!), then switch to IF:

SELECT IF(`id` IS NULL, 0, `id`) FROM `table`

Upvotes: 62

a'r
a'r

Reputation: 37009

There are two ways that you can implement the same logic as a ternary operator:

  1. Use the IF function, eg. IF(expression, true result, false result)

  2. Use the CASE expression, eg. 

    CASE WHEN expression THEN <true result> ELSE <false_result> END

When you are checking for NULL then you can use the IFNULL or COALESCE functions, eg.

IFNULL(ID, 0)
COALESCE(ID, 0)

Upvotes: 28

Dewasish Mitruka
Dewasish Mitruka

Reputation: 2896

Try this :

select if(Id is null, 0, id) as Id;

Upvotes: 202

Related Questions