SQL中ON子句搭配BETWEEN是否合规？示例代码存疑

很多人误以为ON子句只能用来做“表与表之间的主键/外键等值匹配”，但实际上ON子句的核心是定义两个数据集之间的连接匹配规则，只要是能返回布尔值的逻辑表达式，都可以作为ON的条件，范围匹配（比如BETWEEN）当然也完全合法。

拆解示例代码的逻辑 #

先看示例里的两个子查询：

1. pymnt子查询：从payment表统计每个客户的总支付金额tot_payments
2. pymnt_grps子查询：用UNION ALL生成了三个支付金额区间分组，每个分组有名称、下限和上限

然后用INNER JOIN连接这两个数据集，ON子句的pymnt.tot_payments BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit，本质是把每个客户的总支付金额，匹配到对应的区间分组中，这是一种典型的「范围连接」技巧，用来实现“按自定义区间统计分组”的需求。

为什么这么写更好 #

如果不用这种连接写法，你可能需要用一堆CASE WHEN来判断每个客户属于哪个区间：

SELECT 
  CASE 
    WHEN tot_payments BETWEEN 0 AND 74.99 THEN 'Small Fry'
    WHEN tot_payments BETWEEN 75 AND 149.99 THEN 'Average Joes'
    WHEN tot_payments >=150 THEN 'Heavy Hitters'
  END name,
  COUNT(*) num_customers
FROM (
  SELECT customer_id, SUM(amount) tot_payments
  FROM payment
  GROUP BY customer_id
) pymnt
GROUP BY name;

对比之下，用JOIN+区间表的写法更清晰：

• 区间规则集中在pymnt_grps里，修改区间时不需要改动主查询逻辑
• 当区间数量增多时，这种写法的扩展性更好，不用不断加CASE分支

示例代码格式化 #

SELECT pymnt_grps.name, count(*) num_customers
FROM
(SELECT customer_id,
 count(*) num_rentals, sum(amount) tot_payments
FROM payment
GROUP BY customer_id
) pymnt
INNER JOIN
(SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit
UNION ALL
SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit
UNION ALL
SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit
) pymnt_grps
ON pymnt.tot_payments BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit
GROUP BY pymnt_grps.name;

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