SQL查询表前缀与JOIN类型疑问（基于BigQuery公共数据集）

你的查询代码 #

WITH  
 longest_used_bike AS (
  SELECT bike_id,
  SUM (duration_minutes) AS trip_duration
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` 
  GROUP BY bike_id
  ORDER BY trip_duration DESC
  LIMIT 1 )
SELECT 
trips.start_station_id, ---- this line
COUNT (*) AS trip_ct
FROM 
longest_used_bike AS longest
INNER JOIN
 `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS trips
ON longest.bike_id = trips.bike_id
GROUP BY 
trips.start_station_id --- and this line
ORDER BY
trip_ct DESC
LIMIT 1

疑问1：省略trips.前缀结果一致的原因 #

• 当查询涉及的多张表没有同名字段时，SQL会自动识别字段归属。这里longest_used_bike表只有bike_id和trip_duration两个字段，不存在start_station_id，所以即便不写trips.，数据库也能明确你要的是trips表的start_station_id，结果自然一致。
• 讲师要求加前缀是规范写法：后续如果查询新增其他表，刚好出现同名字段，就会触发歧义报错；提前加前缀既能避免这类问题，也能让代码可读性更强，一眼就能看出字段来自哪个表。

疑问2：改用OUTER JOIN结果仍相同的原因 #

不是操作错误，是当前数据特性导致的：

• 你在longest_used_bike里用LIMIT 1只取了一辆车的记录，而这辆车的所有行程本身就来自bikeshare_trips表，必然能在该表中匹配到对应数据。
• 不管用INNER JOIN还是OUTER JOIN（比如LEFT JOIN），左边的longest_used_bike只有一条有效记录，且这条记录的bike_id能在右边trips表中匹配到全部相关行程，两种JOIN的结果集完全一致，最终统计结果自然相同。
• 要是你把longest_used_bike的LIMIT调大，或者存在某个bike_id在trips表里无对应记录（不过这个数据集里不可能出现，因为longest_used_bike的记录就是从trips统计来的），这时OUTER JOIN和INNER JOIN的结果才会产生差异。

内容的提问来源于stack exchange，提问作者Angel Acevedo García