R中sqldf无关联键JOIN行为及聚合值重复填充疑问

问题背景 #

在R中使用sqldf库筛选flights表，已通过查询计算出distance列的平均值和中位数并分别存入average、median表，执行JOIN时遇到两个疑问：

1. 未指定两个表的关联键为何能执行INNER JOIN？
2. JOIN后所有行都填充了中位数和平均值，原因是什么？

相关代码：

library(sqldf)

average = sqldf('SELECT AVG(distance) AS avg FROM flights')

median = sqldf('SELECT MEDIAN(distance) AS med FROM flights')

result = sqldf(
    "SELECT flights.* 
     FROM flights 
     INNER JOIN average_q1 ON flights.distance 
     INNER JOIN median_q1 ON flights.distance 
     WHERE distance BETWEEN median_q1.med AND average_q1.avg"
)

问题解答 #

1. 未指定关联键仍能执行INNER JOIN的原因 #

SQL的JOIN语法中，ON子句支持任意布尔表达式，并非强制要求写两表列相等的关联条件。你这里的ON flights.distance会被当作布尔判断：

• 只要flights.distance的值非0、非空，表达式结果就为TRUE
• 表达式为真时，就会匹配JOIN另一侧表中的行

由于average_q1和median_q1都是仅含一行数据的表（存单个统计值），所以flights的每一行都会和这两个表的唯一行匹配，本质上等同于执行了交叉连接（CROSS JOIN）。

2. JOIN后所有行都填充中位数和平均值的原因 #

因为average_q1和median_q1各自只有一行数据（分别是distance的平均值、中位数），通过上述恒真条件JOIN时，flights的每一行都会和这两个表的唯一行匹配，相当于把单个统计值"广播"到了flights的所有行上，所以所有行都会带上这两个值。

代码修正提示 #

你的代码存在命名不一致问题：创建的表是average和median，但JOIN时用的是average_q1和median_q1，这会导致sqldf找不到对应表。需要统一命名，比如修改创建语句：

average_q1 = sqldf('SELECT AVG(distance) AS avg FROM flights')
median_q1 = sqldf('SELECT MEDIAN(distance) AS med FROM flights')

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