如何在Teradata SQL中用LEFT JOIN替代硬编码实现多CASE语句多值匹配？

问题背景 #

现有Table2表结构及数据：

原本插入目标表3的SQL采用硬编码条件：

INSERT INTO TargetTable3
SELECT 
    VehID,
    CASE WHEN
            Fuel IN ('El', 'Ga')
        AND Color IN ('Red', 'Blue', 'White', 'Black')
        THEN 'Y'
        ELSE 'N' END AS BuyInD
FROM Table2;

需将硬编码的Fuel和Color条件替换为Lookup表Table1，其结构及数据如下：

Lookup参考表：Table1 #

用户尝试用内连接改写时出现逻辑错误（产生笛卡尔积，无法实现正确的多值匹配），错误SQL如下：

INSERT INTO TargetTable3
SELECT 
    VehID,
    CASE WHEN
            Fuel = b.FuelType
        AND Color = b.Color
        THEN 'Y'
        ELSE 'N' END AS BuyInD
FROM Table2 a, Table1 b;

实际业务中的复杂SQL（包含多表关联和多CASE分支）：

SELECT 
    a.VehID,
    b.begin_dte,
    b.end_dte,
    c.comp_name,
    c.comp_addr,
    CASE WHEN a.CarType IN ('SUV')
        AND a.Fuel IN ('El', 'Ga')
        AND a.Color IN ('Red', 'Blue')
        THEN 'Y'
        WHEN a.CarType IN ('SUV')
        AND a.FuelMod1 IN ('El', 'Ga')
        AND a.ColorMod1 IN ('Red', 'Blue')
        THEN 'Y'
        WHEN a.CarType IN ('SUV')
        AND a.FuelMod2 IN ('El', 'Ga')
        AND a.ColorMod2 IN ('Red', 'Blue')
        THEN 'Y'
        ELSE 'N' END AS BuyInD
FROM Cars a,
     Saledate b, 
     comp c
WHERE a.VehID = b.VehID
  AND date BETWEEN b.begin_dte AND b.end_dte
  AND a.VehID = c.VehID;

需通过LEFT JOIN结合Lookup表实现上述多CASE语句的多值匹配逻辑。

解决方案 #

核心思路是用EXISTS子查询（或LEFT JOIN配合聚合判断）替代硬编码，检查字段组合是否存在于Lookup表中，同时保留原多表关联逻辑。

方案1：使用EXISTS子查询（推荐，逻辑清晰性能优） #

SELECT 
    a.VehID,
    b.begin_dte,
    b.end_dte,
    c.comp_name,
    c.comp_addr,
    CASE 
        -- 匹配原始Fuel+Color组合
        WHEN a.CarType = 'SUV'
             AND EXISTS (
                 SELECT 1 
                 FROM Table1 t1
                 WHERE t1.FuelType = a.Fuel 
                       AND t1.Color = a.Color
             )
        THEN 'Y'
        -- 匹配FuelMod1+ColorMod1组合
        WHEN a.CarType = 'SUV'
             AND EXISTS (
                 SELECT 1 
                 FROM Table1 t1
                 WHERE t1.FuelType = a.FuelMod1 
                       AND t1.Color = a.ColorMod1
             )
        THEN 'Y'
        -- 匹配FuelMod2+ColorMod2组合
        WHEN a.CarType = 'SUV'
             AND EXISTS (
                 SELECT 1 
                 FROM Table1 t1
                 WHERE t1.FuelType = a.FuelMod2 
                       AND t1.Color = a.ColorMod2
             )
        THEN 'Y'
        ELSE 'N' 
    END AS BuyInD
FROM Cars a
JOIN Saledate b ON a.VehID = b.VehID
JOIN comp c ON a.VehID = c.VehID
WHERE date BETWEEN b.begin_dte AND b.end_dte;

关键说明

• 每个WHEN分支的硬编码条件替换为EXISTS子查询，验证当前字段组合是否在Table1的有效组合中。
• 避免笛卡尔积问题：EXISTS仅判断匹配是否存在，不会因为Lookup表的多条匹配导致主表记录重复。

方案2：使用LEFT JOIN+GROUP BY去重 #

若偏好使用LEFT JOIN，可通过聚合函数判断是否存在匹配，再分组去重：

SELECT 
    a.VehID,
    b.begin_dte,
    b.end_dte,
    c.comp_name,
    c.comp_addr,
    CASE 
        WHEN MAX(
            CASE 
                WHEN a.CarType = 'SUV' AND (
                    (t1.FuelType = a.Fuel AND t1.Color = a.Color)
                    OR (t1.FuelType = a.FuelMod1 AND t1.Color = a.ColorMod1)
                    OR (t1.FuelType = a.FuelMod2 AND t1.Color = a.ColorMod2)
                ) THEN 1
                ELSE 0
            END
        ) = 1 THEN 'Y'
        ELSE 'N'
    END AS BuyInD
FROM Cars a
JOIN Saledate b ON a.VehID = b.VehID
JOIN comp c ON a.VehID = c.VehID
LEFT JOIN Table1 t1 ON 
    (t1.FuelType = a.Fuel AND t1.Color = a.Color)
    OR (t1.FuelType = a.FuelMod1 AND t1.Color = a.ColorMod1)
    OR (t1.FuelType = a.FuelMod2 AND t1.Color = a.ColorMod2)
WHERE date BETWEEN b.begin_dte AND b.end_dte
GROUP BY a.VehID, b.begin_dte, b.end_dte, c.comp_name, c.comp_addr;

关键说明

• 通过LEFT JOIN关联所有可能匹配的Lookup表记录。
• 用MAX函数判断是否存在任意一条有效匹配，最后通过GROUP BY保留主表每条记录的唯一结果。

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