LEFT OUTER JOIN to check for non-existence relation id-CodePudding

I have sql query part

left outer join 
(
    select `id`,
        `order_payment_id`,
        `product_id`,
        `product_slug_id`,
        `order_package_id`,
        `name`,
        `price`
    from `web`.`order_products` `$Table`
) `$Inner` on (`$Outer`.`id2` = `$Inner`.`order_payment_id`)

But problem is that if

$Outer`.`id2` is NULL

then i need merge like

`$Inner` on (`$Outer`.`order_payment_id` = `$Inner`.`order_payment_id`)

So how to write right syntax for condition inside ON ? expected result

left outer join 
(
    select `id`,
        `order_payment_id`,
        `product_id`,
        `product_slug_id`,
        `order_package_id`,
        `name`,
        `price`
    from `web`.`order_products` `$Table`
) `$Inner` on ((IF ISNULL(`$Outer`.`id2`) THEN `$Outer`.`order_payment_id` ELSE `$Outer`.`id2`) = `$Inner`.`order_payment_id`)

CodePudding user response：

I'd go for the COALESCE function which returns the first non-null value:

) `$Inner` on COALESCE(`$Outer`.`id2`, `$Outer`.`order_payment_id`) = `$Inner`.`order_payment_id`)

CodePudding user response：

Do the trick

) `$Inner` on (IFNULL(`$Outer`.`order_payment_id2`,`$Outer`.`id2`) = `$Inner`.`order_payment_id`)