google-bigquery - LEFT JOIN 与重复的大查询

Question

我的表有重复的 ID，但我想知道两者之间匹配的 ID。表 1 中的哪些不在表 2 中。

Table1 中的 ID 也是 INTEGER，table2 中的 ID 是 STRING，这就是我使用演员表的原因

SELECT cast(T1.ID as STRING) as ID
FROM `project.dataset.table1` as T1 WHERE ID is not null
LEFT JOIN
SELECT DISTINCT(T2.ID) as ID
FROM `project.dataset.table2` as T2 WHERE ID is not null
ON T1.ID = T2.ID

我分别运行这两个查询并且它们没问题，但是当我尝试创建左连接时它显示此错误

大查询错误：

Syntax error: Expected end of input but got keyword LEFT at [3:1]

我已经从这个问题中尝试过BigQuery Full outer join 产生“left join”结果

#standardSQL
SELECT COUNT(DISTINCT T1.NPI)
FROM `project.dataset.table1` as T1 WHERE NPI is not null
JOIN `project.dataset.table2` as T2 WHERE NPI is not null
ON T1.NPI= T2.NPI

还有更多错误。

你能指导我吗？

标签： google-bigquery

Answer 1

尝试除了：

SELECT cast(T1.ID as STRING) as ID
FROM `project.dataset.table1` as T1 WHERE ID is not null
EXCEPT
SELECT DISTINCT(T2.ID) as ID
FROM `project.dataset.table2` as T2 WHERE ID is not null