How to implement ternary conditional operator in MySQL

Question

I want to implement a ternary conditional statement in MySQL. I have a table in which there is one field identifier. Its value may be zero. I want to show id in ternary conditional format as follows:

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

Is this possible in MySQL?

Answer 1

Try the following:

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

Answer 2

The documentation is your friend; you must read it!

It says:

 IFNULL(expr1,expr2) 

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

And then a lot of examples. This is equivalent to using a ternary conditional with a comparison with NULL and a comparison object as the second operand; what not to use characters ? and : so that you have nothing to do with anything.

So in your case:

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

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

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

Answer 3

There are two ways to implement the same logic as the ternary operator:

• Use the IF function, for example. IF(expression, true result, false result)

• Use a CASE expression, for example.

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

When you check for NULL, you can use the IFNULL or COALESCE functions, for example.

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