CODEWITHSUNDEEP
google-bigquery
VMEscoli
VMEscoli

Reputation: 1232

LEFT JOIN IN BIG QUERY WITH DUPLICATES

My tables have duplicate IDs, but I want to know which are the IDs that match between the two. and which ones from table1 are not in table2.

Also ID in Table1 is INTEGER and in table2 is STRING, that's why I'm using cast

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

I run the two queries separate and they are ok, but it shows this error when I try to create the left join

Big query Error:

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

I've tried from this questions BigQuery Full outer join producing "left join" results

#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

and just more errors.

Could you guide me please?

Upvotes: 1

Views: 1514

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Try EXCEPT instead:

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

Upvotes: 1

Related Questions