Cannot execute IN subquery with uncomparable types STRING and INT64

Question

I don't understand what seems to be the problem of this query.

it says

Cannot execute IN subquery with uncomparable types STRING and INT64 at [7:3]

tried this query but it just didn't work.

SELECT
  station_id,
  name
FROM 
  bigquery-public-data.new_york_citibike.citibike_stations
WHERE
  station_id IN

  (
    SELECT
      start_station_id
    FROM
      bigquery-public-data.new_york_citibike.citibike_trips
    WHERE
      usertype = 'Subscriber'
  )

Answer 1

I assume station_id and start_station_id are not of similar data types. In this case we can cast one of the columns to make both are of same type.

If station_id is string and start_station_id is integer type. I would write the query as below. Hope this helps

SELECT station_id, name FROM bigquery-public-data.new_york_citibike.citibike_stations WHERE station_id IN

( SELECT cast(start_station_id as string) FROM bigquery-public-data.new_york_citibike.citibike_trips WHERE usertype = 'Subscriber' )

Answer 2

I can confirm that CAST as string for station_id works.

SELECT
  station_id,
  name
FROM
bigquery-public-data.new_york.citibike_stations
WHERE
  station_id IN
   (
    SELECT
      CAST (start_station_id AS STRING)
    FROM
    bigquery-public-data.new_york.citibike_trips
    WHERE
      usertype =
    'Subscriber'
    )

Answer 3

I had the same problem, the station_id is a STRING and the start_station_id INTEGER, so you need to CAST one of them.

Answer 4

I had to find different columns to target since I got 0 results casting start_station_id and station_id failed to cast. Background: station_id is a string type with values like 06439006-11b6-44f0-8545-c9d39035f32a start_station_id is an Integer type with values like 520

The columns latitude and start_station_latitude do line up and conceptually answer the question, so I did this and got an actual list:

SELECT 
  station_id,
  name
FROM 
  `bigquery-public-data.new_york_citibike.citibike_stations`
WHERE
  latitude IN
  (
      SELECT
        start_station_latitude
      FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
    WHERE
      usertype =
    'Subscriber'
  )