您可以在ON子句中使用运算子OR：

SELECT ....
FROM Orders o LEFT JOIN Addresses a
ON a.id = o.delivery_address_id 
OR (o.delivery_address_id IS NULL AND a.id = o.invoice_address_id);

或者，使用COALESCE()：

SELECT ....
FROM Orders o LEFT JOIN Addresses a
ON a.id = COALESCE(o.delivery_address_id, o.invoice_address_id);

所以，你想在delivery_address_id上加入，但有时它是NULL，所以你需要invoice_address_id作为后备。
这些情况是COALESCE函数真正发挥作用的地方。如果不是NULL，COALESCE(delivery_address_id, invoice_address_id)将解析为交货地址ID，否则将解析为发票地址ID。
这样我们就可以实现您想要的联接：

SELECT
        orders.some_field,
        addresses.address
      FROM
        orders
 LEFT JOIN
        addresses
        ON
        COALESCE(orders.delivery_address_id, orders.invoice_address_id) = addresses.id