SQL statement equivalent to ternary operator

Question

I would like to create a statement that is equivalent to (x - y == 0) ? return 0 : return 100 in MySQL. Something that might look like this:

SELECT id, [(integer_val - 10 == 0) ? 0 : 100] AS new_val FROM my_table

I want to compare an attribute in each row to a certain number, and if the difference between that number and the number in the row is 0, I want it to give me 0, otherwise, I want it to give me 100.

Example:

Applying this query on my_table (with 10 being the 'compared to' number):

id  |   integer_val
===================
1   10
2   10
3   3
4   9

Would return this:

id  |   new_val
===================
1   100
2   100
3   0
4   0

How can I do this?

Answer 1

Try this:

SELECT id, IF(integer_val = 10, 100, 0) AS new_val 
FROM my_table;

OR

SELECT id, (CASE WHEN integer_val = 10 THEN 100 ELSE 0 END) AS new_val 
FROM my_table;

Answer 2

Use case when statement:

select *, (case when integer_val = 10 then 100 else 0 end) as New_Val 
from yourtable

Answer 3

Try using the IF function:

SELECT id, IF(integer_val - 10 = 0, 0, 100) AS new_val FROM my_table

(I stuck with your condition expression, but it can be simplified a bit since integer_value - 10 = 0 has exactly the same truth value as integer_value = 10.)

Note that the IF function is different from MySQL's IF statement used for stored programs.