如何基于id+territory差异获取PostgreSQL/BigQuery全字段数据？

我有两个从CSV文件导入的表this_week（新表）和last_week（旧表），需要基于id+territory组合键找出this_week中存在但last_week中不存在的记录。目前已经能用EXCEPT语句获取到差异的id和territory：

SELECT id, territory FROM this_week EXCEPT SELECT id, territory FROM last_week

但我需要获取这些差异记录对应的所有字段，请问在PostgreSQL或BigQuery中该如何实现？

在PostgreSQL和BigQuery中，有两种常用且高效的方法可以实现需求：

方法1：使用NOT EXISTS子查询 #

这是逻辑清晰、性能表现优异的写法，直接筛选this_week里未在last_week中匹配到id+territory的记录，返回完整字段：

WITH this_week (id,territory,name,other) AS (VALUES(1,'us','titanic','uhd'),(22,'us','spider','hd'),(3,'fr','new','hd')),
     last_week (id,territory,name,other) AS (VALUES(1,'us','titanic','uhd'),(2,'us','spider','hd'))
SELECT *  -- 返回this_week的所有字段
FROM   this_week t
WHERE  NOT EXISTS (
   SELECT * FROM last_week l
   WHERE  t.id = l.id
   AND    t.territory = l.territory
   );

执行后会返回this_week中(22,'us')和(3,'fr')对应的完整记录。

方法2：使用LEFT JOIN结合IS NULL #

通过左连接关联两张表，筛选出last_week中匹配字段为NULL的记录，即this_week独有的数据：

WITH this_week (id,territory,name,other) AS (VALUES(1,'us','titanic','uhd'),(22,'us','spider','hd'),(3,'fr','new','hd')),
     last_week (id,territory,name,other) AS (VALUES(1,'us','titanic','uhd'),(2,'us','spider','hd'))
SELECT t.*  -- 返回this_week的所有字段
FROM   this_week t
LEFT JOIN last_week l ON t.id = l.id AND t.territory = l.territory
WHERE  l.id IS NULL;

该写法和NOT EXISTS效果完全一致，两种方式在PostgreSQL和BigQuery中都能稳定执行，可根据个人习惯选择。

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