DB2 Join with case when 語句或聯合

如何在連接中使用 case 語句？

給定：3張桌子

經銷商

DEALER_NO CUST_ID
001       100
002       200 
003       300

表：客戶

CUSTOMER_NO  NAME   CUSTOMER_MAIL
100          JOHN           1
200          MIKE           0
300          TERRY          1
400          ANTON          1
500          SUSAN          0

命令

ID     DEALER_ID     CUSTOMER_ID     MAIL
1      001                           2
2      002                           0
3      003                           0
4                        400         0
5                        500         0

如何在結果中顯示 customer.customer_mail = ‘1’ 和 order.mail = ‘0’ 的所有訂單。所以查詢結果應該只有訂單 ID 3 和 4。

請注意，所有經銷商都連結到 1 個客戶，但也有沒有經銷商程式碼的客戶編號。

使用我的簡單語句，我只能搜尋帶有dealer_id 或customer_id 的訂單，不能同時搜尋。哪個語句會更快，加入 case 或 union？

SELECT * 
 FROM ORDER
 LEFT OUTER JOIN DEALER 
   ON DEALER_NO = DEALER_ID
 LEFT OUTER JOIN CUSTOMER 
   ON CUST_ID = CUSTOMER_NO 
  AND CUSTOMER_NO = CUSTOMER_ID
WHERE CUSTOMER.CUSTOMER_MAIL = '1' 
  AND ORDER.MAIL = '0'

JOIN 中的 OR 是解決方案。

SELECT * 
  FROM ORDER
  LEFT OUTER JOIN DEALER 
    ON DEALER_NO = DEALER_ID
  LEFT OUTER JOIN CUSTOMER 
    ON CUST_ID = CUSTOMER_NO OR CUSTOMER_NO = CUSTOMER_ID
 WHERE CUSTOMER.CUSTOMER_MAIL = '1' 
   AND ORDER.MAIL = '0'

使用您的查詢，您是否嘗試過這種 where 條件

WHERE (CUSTOMER.CUSTOMER_MAIL = '1' AND ORDER.MAIL = '0')
  OR (CUSTOMER.CUSTOMER_MAIL = '1' AND ORDER.MAIL is NULL)
  OR (CUSTOMER.CUSTOMER_MAIL is NULL AND ORDER.MAIL = '0')

引用自：https://dba.stackexchange.com/questions/224482