MySQL LEFT JOIN查询如何输出带NULL值的预期匹配结果

实现方案 #

核心逻辑 #

你需要先将所有物料和所有已存在的订单做全量组合，再关联订单与物料的匹配关系，不存在的匹配项自动返回NULL值。

实现SQL #

SELECT a.materialid, a.materialname, b.`order`, b.materialid as test2_materialid
FROM test1 a
-- 取所有不重复的订单号，和物料做全量组合
CROSS JOIN (SELECT DISTINCT `order` FROM test2) all_orders
-- 同时按物料ID和订单号关联订单表
LEFT JOIN test2 b 
ON a.materialid = b.materialid AND all_orders.`order` = b.`order`
-- 可选：排序逻辑和你给出的期望结果顺序完全对齐
ORDER BY 
  CASE a.materialname 
    WHEN 'apple' THEN 1
    WHEN 'pear' THEN 2
    WHEN 'balanna' THEN 3
  END,
  b.`order` IS NULL DESC,
  b.`order` DESC;

逻辑说明 #

• CROSS JOIN 会将test1的3条物料数据和test2去重后的2个订单号做笛卡尔积，得到6行基础数据，和你期望的结果行数一致
• 左关联test2时同时匹配materialid和order两个字段，只有test2中同时存在对应物料+订单的记录才会返回test2的字段值，否则返回NULL
• 注意order是SQL保留关键字，查询时需要用反引号包裹避免语法错误

内容的提问来源于stack exchange，提问作者Mike