Bigquery Scalar subquery produced more than one element-CodePudding

I have a scalar subquery that looks up city name based on a city code and state code. Sometimes the subquery does not return a match and this is causing an error: 'Scalar subquery produced more than one element'. I verified that there are no duplicates in the lookup table (city code state code is unique).

WITH city_names AS (
    SELECT  city_abbr,state_code,city_name
FROM  `myproject.mydataset.city_lookup_table`
  )

SELECT  
consumerId, 
(SELECT city_name from city_names WHERE city_names.city_abbr = city_code AND city_names.state_code = state_code) AS cityName_new 

FROM (
 SELECT consumerId, city_code,state_code
FROM `myproject.mydataset.Customers` 
)

I want the subquery to return city_name = NULL if there are no matching city_abbr and state_id. Currently Bigquery returns "no rows", which is causing the main query to fail.

CodePudding user response：

just simply add ARRAY in front of that subquery!