-1
WITH 
longest_used_bike AS (
    SELECT 
        bikeid,
        SUM(duration_minutes) AS trip_duration
    FROM 
        `bigquery-public-data.austin_bikeshare.bikeshare_trips`
    GROUP BY 
        bikeid
    ORDER BY 
        trip_duration DESC 
    LIMIT 1
)

-- find station at which longest_used bike leaves most often

SELECT 
trips.start_station_id,
COUNT(*) AS trip_ct
FROM 
longest_used_bike AS longest
INNER JOIN 
`bigquery-public-data.austin_bikeshare.bikeshare_trips` AS trips
ON longest.bikeid = trips.bikeid
GROUP BY 
trips.start_station_id
ORDER BY
trip_ct DESC 
LIMIT 1

this query will give you a result thats 2575 but why does the result change to 3798 when you use full join instead of inner join? im trying to figure that one what but i am not sure what to think

philipxy
  • 14,867
  • 6
  • 39
  • 83
Abdur Rahman Sayeed
  • 1
  • 1
  • 1
    Please create a reproduction on http://sqlfiddle.com/ so we as well as you can fully test and experiment – Toumash Aug 24 '21 at 07:41
  • Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) – philipxy Jul 25 '22 at 15:59

2 Answers2

0

An INNER JOIN will return only rows where the JOIN condition is satisfied. So only rows where there us a natch in both tables.

A FULL JOIN will return ALL rows from the left and all rows from the right with null values in the fields where there is not a natch.

elcortegano
  • 2,444
  • 11
  • 40
  • 58
antonjk
  • 16
  • 1
0

A full join will include all entries from the trips table - regardless of whether or not they are joinable to the longest_used_bike ID (they will have a NULL value for the columns in longest)

Also see here for an explanation on join-types.

A tip: If you encounter things like these try to look at the queries unaggregated (omit the GROUP BY clause and the COUNT function) - you would then notice here that you'll suddenly have more (unwanted) rows in the FULL JOIN query.

ksbawpn
  • 302
  • 4
  • 19