GoogleSQL BigQuery中使用WHERE子句结合UNION遇未识别名称错误求助

Let's break down what's going wrong here and fix it step by step.

The Root Cause #

Your error pops up because you’re referencing alias t2 in the first subquery (FROM table_1 AS t1 WHERE t2.e_id <> t1.e_id), but t2 is only defined in the second subquery of your UNION DISTINCT. Those two subqueries operate independently—they can’t access each other’s table aliases. So when the query runs, the first subquery has no idea what t2 refers to.

What Your Code Likely Intends to Do #

From your code structure, it looks like you want to:

• Combine data from table_1 and table_2
• Convert the fate_result field to a string in both datasets
• Exclude records from table_1 where the e_id matches any e_id in table_2
• Remove duplicate records using UNION DISTINCT

If that’s the case, here’s the corrected query:

CREATE OR REPLACE TABLE table_1 
OPTIONS ( quota_accounting_owner='sxxx@gmail.com') 
AS 
SELECT * FROM (
  -- Get records from table_1 where e_id doesn't exist in table_2
  SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result 
  FROM table_1 AS t1 
  WHERE NOT EXISTS (
    SELECT 1 FROM table_2 t2 WHERE t2.e_id = t1.e_id
  )
  UNION DISTINCT
  -- Get all records from table_2 (with fate_result converted to string)
  SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result 
  FROM table_2 AS t2
);

Key Fixes Made: #

• Replaced the invalid cross-subquery t2.e_id <> t1.e_id check with a NOT EXISTS clause. This properly checks for non-matching e_ids by explicitly referencing table_2 within the first subquery’s scope.
• Ensured each subquery only uses tables/aliases that are defined in its own context, eliminating the "unrecognized name" error.

If your original goal was different (like joining the tables instead of unioning them), feel free to clarify and I can adjust the solution further!

内容的提问来源于stack exchange，提问作者Mask