how to convert this OR operator to case statement-CodePudding

i have the below query, i want to know about the case statement by changing the below type of operator.

SELECT t_product.a_productid,
t_product.a_mpactive,
t_product.a_active,
trim(substring_index(a_reference,'_',-1)) as a_reference,
t_product.a_shopid,
t_productlang.a_name,
t_deactivatedproduct.a_reason
FROM t_deactivatedproduct
inner join
(SELECT max(a_deactivatedproductid) as a_deactivatedproductid
FROM t_deactivatedproduct
GROUP by t_deactivatedproduct.a_productid) as a on a.a_deactivatedproductid = t_deactivatedproduct.a_deactivatedproductid
INNER JOIN t_product ON t_product.a_productid = t_deactivatedproduct.a_productid
INNER JOIN t_productlang ON t_product.a_productid = t_productlang.a_productid AND t_product.a_shopid IN(2,3,5,6,7,10,8,15,12,16,17,26,27,28)
WHERE t_product.a_ispublished = 1
AND ((t_product.a_active = 1 AND t_product.a_mpactive = 0)
OR (t_product.a_active = 0 AND t_product.a_mpactive = 1)
OR (t_product.a_active = 0 AND t_product.a_mpactive = 0))
ORDER BY t_deactivatedproduct.a_deactivatedproductid DESC limit 700;

CodePudding user response：

CASE is probably not the best pick to express such boolean logic. MySQL, however, understands tuple equality, so you could rewrite this:

AND (
    (t_product.a_active = 1 AND t_product.a_mpactive = 0)
    OR (t_product.a_active = 0 AND t_product.a_mpactive = 1)
    OR (t_product.a_active = 0 AND t_product.a_mpactive = 0)
)

As:

AND (t_product.a_active, t_product.a_mpactive) in ( (1, 0), (0, 1), (0, 0) )

That will make the query neater, but not necessarily faster. Under the hood, the optimizer rewrites the query and both expressions might be ultimately interpreted similarly.

If we were to phrase this with CASE, we would have to wrap it somehow, to make it look like a predicate. Something like:

AND 1 = CASE
    WHEN t_product.a_active = 1 AND t_product.a_mpactive = 0 THEN 1
    WHEN t_product.a_active = 0 AND t_product.a_mpactive = 1 THEN 1
    WHEN t_product.a_active = 0 AND t_product.a_mpactive = 0 THEN 1
END

... And, for what it's worth... If both columns always store non-null boolean values (0/1), then the condition is simpler phrased as a negation:

WHERE NOT (t_product.a_active = 1 AND t_product.a_mpactive = 1)

Or:

WHERE (t_product.a_active, t_product.a_mpactive) not in ( (1, 1) )