在BigQuery中用表名前缀连接表后，如何在SELECT EXCEPT()中处理索引列？

我来帮你搞定这个BigQuery里的实操问题！先回顾下你的场景：用FULL JOIN USING后，查询结果里的连接键（KEY1/KEY2/KEY3）会以table_A.KEY1、table_B.KEY1这种带表名前缀的形式存在，现在要通过SELECT EXCEPT()来处理这些索引列对吧？下面分几种常见场景给你具体方案：

1. 排除单个带前缀的索引列 #

如果只是想排除某一张表的某个连接键，比如去掉table_A.KEY1，直接在EXCEPT里写完整的带前缀列名就行：

SELECT EXCEPT(table_A.KEY1)
FROM (
  -- 先执行带表前缀的连接查询
  SELECT table_A, table_B 
  FROM table_A FULL JOIN table_B USING (KEY1, KEY2, KEY3)
) AS joined_data

这里子查询返回的列都是带表前缀的，所以EXCEPT里必须用完整的列名，不能只写KEY1——因为此时结果里没有叫KEY1的列，只有两个带前缀的版本。

2. 排除某张表的所有索引列 #

如果要去掉某张表的全部连接键（比如table_A的KEY1/KEY2/KEY3），直接把所有带前缀的索引列列在EXCEPT里：

SELECT EXCEPT(table_A.KEY1, table_A.KEY2, table_A.KEY3)
FROM (
  SELECT table_A, table_B 
  FROM table_A FULL JOIN table_B USING (KEY1, KEY2, KEY3)
) AS joined_data

执行后，结果会保留table_A的其他非索引列，以及table_B的所有列（包括它的索引列）。

3. 合并索引列并排除重复的前缀版本 #

如果不想保留两个版本的索引列，而是合并成一个（比如用COALESCE取非空值），同时排除掉带前缀的索引列，可以直接在主查询里处理，不用嵌套子查询：

SELECT 
  -- 合并索引列，优先取table_A的值，没有则用table_B的
  COALESCE(table_A.KEY1, table_B.KEY1) AS KEY1,
  COALESCE(table_A.KEY2, table_B.KEY2) AS KEY2,
  COALESCE(table_A.KEY3, table_B.KEY3) AS KEY3,
  -- 排除table_A的索引列，保留其他列
  EXCEPT(table_A.*, KEY1, KEY2, KEY3),
  -- 排除table_B的索引列，保留其他列
  EXCEPT(table_B.*, KEY1, KEY2, KEY3)
FROM table_A FULL JOIN table_B USING (KEY1, KEY2, KEY3)

这种方式既得到了合并后的干净索引列，又保留了两个表的非索引数据，非常实用。

注意事项 #

• 列名有特殊字符时，记得用反引号括起来，比如table_A.User ID``；
• SELECT EXCEPT()不支持正则匹配列名，所以需要明确列出要排除的带前缀列；
• 如果不确定结果里的列名，可以先运行子查询查看具体的列名列表，再编写EXCEPT语句。

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