主连接条件不满足时，如何通过次要条件关联表获取商品定价？

问题根源 #

当前查询的JOIN逻辑完全没实现「主条件不匹配时用次要条件」的需求：

• 那个CASE语句等价于直接写PRICING.colour = PRODUCTS.colour，因为只有当两者相等时才返回PRODUCTS.colour，否则CASE返回null，而SQL中null = 任何值的结果都是unknown，不会匹配成功。
• 整个查询只尝试了精确匹配style+colour的关联，完全没有处理「找不到对应colour时，匹配同style下colour为null的默认价格」的逻辑，所以apple purple的行无法关联到默认价格。

正确实现方案 #

方案1：两次LEFT JOIN + COALESCE合并结果 #

先分别关联精确匹配的价格和默认价格，再用COALESCE优先取精确匹配的结果，没有的话用默认价格：

SELECT 
    p.style AS PRODUCTS_style,
    p.colour AS PRODUCTS_colour,
    p.size AS PRODUCTS_size,
    COALESCE(p1.style, p2.style) AS PRICING_style,
    COALESCE(p1.colour, p2.colour) AS PRICING_colour,
    COALESCE(p1.price, p2.price) AS PRICING_price
FROM dummy_prods p
-- 先关联精确匹配的style+colour
LEFT JOIN dummy_pricing p1 
    ON p.style = p1.style AND p.colour = p1.colour
-- 再关联同style下的默认价格（colour为null）
LEFT JOIN dummy_pricing p2 
    ON p.style = p2.style AND p2.colour IS NULL
-- 可选：过滤掉完全没有匹配价格的行（比如orange那条）
WHERE COALESCE(p1.price, p2.price) IS NOT NULL

方案2：窗口函数排序取优先级最高的匹配 #

通过窗口函数给匹配结果打优先级（精确匹配的优先级高于默认匹配），然后取每个商品行的最高优先级匹配：

WITH ranked_pricing AS (
    SELECT 
        p.style AS PRODUCTS_style,
        p.colour AS PRODUCTS_colour,
        p.size AS PRODUCTS_size,
        pr.style AS PRICING_style,
        pr.colour AS PRICING_colour,
        pr.price AS PRICING_price,
        -- 精确匹配的行排第1，默认匹配排第2
        ROW_NUMBER() OVER (
            PARTITION BY p.style, p.colour, p.size 
            ORDER BY CASE WHEN pr.colour = p.colour THEN 1 ELSE 2 END
        ) AS rn
    FROM dummy_prods p
    LEFT JOIN dummy_pricing pr 
        ON p.style = pr.style 
        AND (pr.colour = p.colour OR pr.colour IS NULL)
)
SELECT 
    PRODUCTS_style, PRODUCTS_colour, PRODUCTS_size,
    PRICING_style, PRICING_colour, PRICING_price
FROM ranked_pricing
WHERE rn = 1
-- 可选：过滤掉无价格的行
AND PRICING_price IS NOT NULL

说明 #

• 方案1逻辑简单直观，适合规则单一的场景；
• 方案2扩展性更强，如果后续需要添加更多优先级的匹配规则，只需修改ORDER BY中的CASE逻辑即可。

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