为何在同表关联场景中使用WHERE而非JOIN ON？

这种在SELECT子句内的子查询叫做相关子查询，它会和外层查询的每一行数据联动：

• 外层查询每取出一行（比如某条骑行记录，对应outer_trips中的一个start_station_id），子查询就会用这个start_station_id作为过滤条件，计算该站点所有骑行记录的平均时长。
• 如果你去掉WHERE子句，子查询会直接计算整张表所有骑行记录的平均时长，此时所有行的avg_duration_for_stations都会是同一个固定值，而非每个站点各自的平均值。

这个WHERE的作用确实类似JOIN的ON条件：它建立了外层查询的outer_trips.start_station_id与子查询同表start_station_id的关联，让子查询只为当前行对应的站点计算统计值。

举个简单例子：假设外层行的start_station_id是123，子查询就只筛选表中所有start_station_id=123的记录计算平均值；如果外层行是456，子查询会自动切换为筛选456的记录，以此类推。

你也可以用JOIN写法实现同样效果，先预计算每个站点的平均时长再和原表关联：

SELECT
    t.starttime,
    t.stoptime,
    t.start_station_id,
    t.tripduration,
    ROUND(s.avg_duration, 2) AS avg_duration_for_stations,
    ROUND(t.tripduration - s.avg_duration, 2) AS duration_diff
FROM `bigquery-public-data.new_york_citibike.citibike_trips` t
JOIN (
    SELECT start_station_id, AVG(tripduration) AS avg_duration
    FROM `bigquery-public-data.new_york_citibike.citibike_trips`
    GROUP BY start_station_id
) s ON t.start_station_id = s.start_station_id

两种写法效果一致，但相关子查询的写法更直观，适合这种为每行计算对应分组统计值的场景。

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