如何使用条件语句实现无直接关联字段的SQL两表关联查询

首先需要调整两个核心逻辑错误：

1. 要保留Table1全部数据，必须使用LEFT JOIN而非INNER JOIN，否则匹配不到Table2的行（比如示例里SN=100的记录）会被直接丢弃
2. 逗号分隔的字符串类型SNRange不能直接用IN判断包含关系，需要用对应数据库的字符串匹配函数做校验，同时要做防部分匹配处理（比如避免SN=12误命中123,14这类范围值）

不同数据库的实现写法 #

MySQL #

SELECT 
  t1.SN,
  t1.Customer,
  t2.OrderNum AS WONum,
  t2.`Line Item` AS LineNum
FROM Table1 t1
LEFT JOIN Table2 t2 
  ON FIND_IN_SET(t1.SN, t2.SNRange) > 0;

MySQL自带的FIND_IN_SET(值, 逗号分隔字符串)函数原生支持该场景，返回值大于0代表存在匹配

SQL Server #

SELECT 
  t1.SN,
  t1.Customer,
  t2.OrderNum AS WONum,
  t2.[Line Item] AS LineNum
FROM Table1 t1
LEFT JOIN Table2 t2
  ON CHARINDEX(',' + CAST(t1.SN AS VARCHAR(10)) + ',', ',' + t2.SNRange + ',') > 0;

给字符串前后都加逗号是为了避免部分匹配问题

PostgreSQL #

SELECT 
  t1.SN,
  t1.Customer,
  t2.OrderNum AS WONum,
  t2."Line Item" AS LineNum
FROM Table1 t1
LEFT JOIN Table2 t2
  ON POSITION(',' || t1.SN::TEXT || ',' IN ',' || t2.SNRange || ',') > 0;

Oracle #

SELECT 
  t1.SN,
  t1.Customer,
  t2.OrderNum AS WONum,
  t2."Line Item" AS LineNum
FROM Table1 t1
LEFT JOIN Table2 t2
  ON INSTR(',' || TO_CHAR(t1.SN) || ',', ',' || t2.SNRange || ',') > 0;

性能优化方案（大数据量场景适用） #

如果表数据量较大，上面的字符串匹配方式性能较差，建议提前把Table2的SNRange拆分为单行单SN的结构再关联，以MySQL为例拆分逻辑如下：

WITH RECURSIVE split_sn AS (
  SELECT 
    OrderNum,
    `Line Item`,
    SNRange AS remain,
    SUBSTRING_INDEX(SNRange, ',', 1) AS sn_val
  FROM Table2
  UNION ALL
  SELECT 
    OrderNum,
    `Line Item`,
    SUBSTRING(remain, LENGTH(sn_val) + 2),
    SUBSTRING_INDEX(SUBSTRING(remain, LENGTH(sn_val) + 2), ',', 1)
  FROM split_sn
  WHERE remain LIKE '%,%'
)
SELECT 
  t1.SN,
  t1.Customer,
  s.OrderNum AS WONum,
  s.`Line Item` AS LineNum
FROM Table1 t1
LEFT JOIN split_sn s ON t1.SN = s.sn_val;

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