'MySQL alternative way to `CASE` Statement

May I know, is there any alternative way or short way to write this case statement shown in the below:

case 
  when p.our_price IS NULL OR p.our_price = 0 
   then p.sales_price 
  else p.our_price 
end as sales_price

I tried using mysql built in funciton ifnull as below:

ifnull(p.our_price,p.sales_price)

But it doesn't work for me.

mysqlsqlcase


Solution 1:[1]

You could stick with a CASE expression but use COALESCE to make it more succinct:

CASE COALESCE(p.our_price, 0)
     WHEN 0 THEN p.sales_price ELSE p.our_price END AS sales_price

Or, using the IF() function:

IF(COALESCE(p.our_price, 0) = 0, p.sales_price, p.our_price)

As a long time MySQL user, I often find myself using CASE more than IF() as the former allows omitting the ELSE condition entirely. IF() on the other hand always requires an explicit else value.

Solution 2:[2]

IF(p.our_price IS NULL OR p.our_price = 0, p.sales_price, p.our_price)

the 1st argument is the condition, 2nd and 3rd are the choices in case the condition is true or false.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions