MySQL alternative way to `CASE` Statement-CodePudding

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.

CodePudding user response：

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 choice in case the condition is true or false.

CodePudding user response：

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