使用Cross Join匹配最邻近值：是否有更高效的SQL实现方式？

问题背景 #

现有两张表t1和t2，结构如下：

表t1 #

表t2 #

当前通过CROSS JOIN全量关联两表，再用row_number()排序取最接近的t2.id，但数据量大时全量排序的时间成本极高，需要更高效的替代方案。原实现代码如下：

-- 查找与t1.id对应val最接近的t2.id
WITH cj AS (
SELECT 
  t1.id AS t1_id, t2.id AS t2_id,  
  ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY ABS(t1.val - t2.val)) AS rw
 FROM t1
 CROSS JOIN t2
)
SELECT t1_id, t2_id FROM cj WHERE rw = 1

优化方案 #

1. 通用高效写法（依赖索引） #

核心思路是给t2.val建立B树索引，避免全表关联，对每个t1.val仅定向查找最接近的记录：

SELECT 
  t1.id AS t1_id,
  (SELECT t2.id 
   FROM t2 
   ORDER BY ABS(t2.val - t1.val) 
   LIMIT 1) AS t2_id
FROM t1;

• 效率提升：借助t2(val)的索引，数据库可以快速定位到匹配的记录，时间复杂度从原方案的O(nm)降至O(nlog m)（n为t1行数，m为t2行数）。
• 注意事项：必须确保t2.val字段上存在索引，否则无法发挥优化效果。

2. 数据库特定优化实现 #

PostgreSQL：LATERAL JOIN写法

LATERAL允许子查询引用外部表的字段，可读性更强，同时支持灵活调整返回结果数量：

SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
LEFT JOIN LATERAL (
  SELECT id 
  FROM t2 
  ORDER BY ABS(t2.val - t1.val) 
  LIMIT 1
) t2 ON true;

若需要返回所有差值相同的最接近记录，只需去掉LIMIT，配合RANK()过滤即可。

SQL Server：APPLY + TOP 1写法

SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
CROSS APPLY (
  SELECT TOP 1 id 
  FROM t2 
  ORDER BY ABS(t2.val - t1.val)
) t2;

APPLY的作用等价于PostgreSQL的LATERAL，配合TOP 1快速获取单条匹配记录。

MySQL：子查询+LIMIT写法

与通用写法完全一致，确保t2.val上有索引即可，MySQL会自动利用索引优化排序查找逻辑。

3. 多匹配场景处理 #

如果存在多个t2.val与t1.val的差值绝对值相同的情况，若需要返回所有符合条件的记录，可以用RANK()替代ROW_NUMBER()，结合定向查找优化：

-- PostgreSQL示例：返回所有最接近的记录
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1
LEFT JOIN LATERAL (
  SELECT id, ABS(t2.val - t1.val) AS diff
  FROM t2
) t2 ON true
QUALIFY RANK() OVER (PARTITION BY t1.id ORDER BY diff) = 1;

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