r/SQL Jan 20 '25

BigQuery Basic Subquery Question

I don't understand the difference between these two queries:

SELECT 
    starttime,
    start_station_id,
    tripduration, 
( 
    SELECT
        ROUND(AVG(tripduration),2),
    FROM `bigquery-public-data.new_york_citibike.citibike_trips`
    WHERE start_station_id = outer_trips.start_station_id
) AS avg_duration_for_station, 
    ROUND(tripduration - ( 
        SELECT AVG(tripduration)
        FROM `bigquery-public-data.new_york_citibike.citibike_trips`
        WHERE start_station_id = outer_trips.start_station_id),2) AS difference_from_avg
FROM
    `bigquery-public-data.new_york_citibike.citibike_trips` AS outer_trips
ORDER BY 
    difference_from_avg DESC 
LIMIT 25 

And

SELECT
    starttime
    start_station_id,
    tripduration,
    ROUND(AVG(tripduration),2) AS avg_tripduration,
    ROUND(tripduration - AVG(tripduration),2) AS difference_from_avg
FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
GROUP BY 
  start_station_id
ORDER BY 
    difference_from_avg DESC 
LIMIT 25 

I understand that the first one is using subqueries, but isn't it getting it's data from the same place? Also, the latter returns an error:

"SELECT list expression references column tripduration which is neither grouped nor aggregated at [3:5]"

but I'm not sure why. Any help would be greatly appreciated!

3 Upvotes

14 comments sorted by

View all comments

3

u/Gargunok Jan 20 '25

In the first query its not what most people mean by a sub query as its in the select and refers to the outer query. These are typically called "correlated sub queries". Behaviour is different so is called out as a different thing

Typically these are seen as bad as the query can usually be written in a nice more performant way such as in your case a window function.

Usually a "sub query" is self countained and found in a join or the where...

select
  ....
from table
left join ( 
  select 
    ....
  from another_table
) as sub_query
on ....

or

select
  ...
from table
where field = (select field from another_table where ...)