Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 776
user1086355 Avatar asked Dec 14 '11 14:12

user1086355


People also ask

Can we use ternary operator in MySQL query?

(yourCondition) ? statement1:statement2; In the above syntax, if yourCondition becomes true then statement1 will evaluate and if yourCondition becomes false then statement2 will evaluate.

How can I use ternary operator in SQL?

SELECT (CASE WHEN (condition 1 exp) THEN ( true statement ) WHEN (condition2 exp) THEN ( true statement ) ELSE( false statement ) END) AS "Conditional_ex" FROM table; It evaluates the condition statement and executes first result and the remaining where clause is not executed.

How do you use a ternary conditional operator?

The conditional (ternary) operator is the only JavaScript operator that takes three operands: a condition followed by a question mark ( ? ), then an expression to execute if the condition is truthy followed by a colon ( : ), and finally the expression to execute if the condition is falsy.

What does != Mean in MySQL?

not equal to (<>, !=) operator. MySQL Not equal is used to return a set of rows (from a table) after making sure that two expressions placed on either side of the NOT EQUAL TO (<>) operator are not equal. Syntax: <>, !=


2 Answers

Try this :

select if(Id is null, 0, id) as Id; 
like image 64
Dewasish Mitruka Avatar answered Sep 20 '22 01:09

Dewasish Mitruka


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` 
like image 23
Lightness Races in Orbit Avatar answered Sep 19 '22 01:09

Lightness Races in Orbit