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.
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);