I dont have google big query, but based on my understanding of the question I think you just need a CONCAT in the join condition,let me know.

Fiddle in Mysql

SELECT
    z.county_code,
    z.zip,
    w.temperature
FROM
    zipcodes z
INNER JOIN
    weather w
ON
    z.county_code = CONCAT(w.state_id, w.county_id);