Oracle SQL两表三列关联时C3空值匹配*的关联逻辑实现问题

你可以直接在JOIN的ON条件中通过分支逻辑处理C3的匹配规则，写法如下：

SELECT t1.c1, t1.c2, t1.c3, t2."DESC"
FROM table_1 t1
JOIN table_2 t2 
  ON t1.c1 = t2.c1 
  AND t1.c2 = t2.c2 
  AND (
    (t1.c3 IS NOT NULL AND t1.c3 = t2.c3)
    OR 
    (t1.c3 IS NULL AND t2.c3 = '*')
  );

注意：DESC是Oracle的保留关键字，所以查询时需要用双引号包裹，否则会触发语法错误。Oracle中空字符串会被默认识别为NULL，如果你表中t1.C3的空值是空字符串格式，上述逻辑也可以正常覆盖。

逻辑说明 #

• 先保证C1、C2两列完全匹配，再通过括号包裹的OR分支处理两种C3匹配场景：
  • t1.C3不为空时，直接和t2.C3做等值匹配
  • t1.C3为空时，仅匹配t2中C3为*的记录

用你提供的样例数据执行上述SQL，返回结果如下：

如果你更习惯把关联条件写在WHERE子句中，写法基本一致：

SELECT t1.c1, t1.c2, t1.c3, t2."DESC"
FROM table_1 t1, table_2 t2
WHERE t1.c1 = t2.c1 
  AND t1.c2 = t2.c2 
  AND (
    (t1.c3 IS NOT NULL AND t1.c3 = t2.c3)
    OR 
    (t1.c3 IS NULL AND t2.c3 = '*')
  );

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