Bigquery, Union Issue / Error: Syntax Error: Expected Keyword All or Keyword Distinct but Got Keyword Select at [16:3]
Currently, I'm Trying to Connect 4 Tables Together and Display Only 5 Columns and Union Them All to One Table. This Is the Query: Select Id, Platform, Url...
![Bigquery, Union Issue / Error: Syntax Error: Expected Keyword All or Keyword Distinct but Got Keyword Select at [16:3]](https://www.alledaags.nl/uploads/bigquery-union-issue-error-syntax-error-expected_1200.jpg)
Currently, I'm trying to connect 4 tables together and display only 5 columns and union them all to one table.
This is the query:
SELECT
id,
platform,
url,
profileImageUrl,
name
FROM (
SELECT
f.id AS id,
f.platform AS platform,
f.url AS url,
f.profileImageUrl AS profileImageUrl,
f.name AS name
FROM
`l.p.link_f_main` AS f UNION
SELECT
i.id AS id,
i.platform AS platform,
i.url AS url,
i.profilePicture AS profileImageUrl,
i.fullName AS name
FROM
`l.p.link_i_main` AS i UNION
SELECT
t.id AS id,
t.platform AS platform,
t.url AS url,
t.profileImageUrl AS profileImageUrl,
t.name AS name
FROM
`l.p.link_t_main` AS t UNION
SELECT
y.id AS id,
y.platform AS platform,
y.url AS url,
y.profileImageUrl AS profileImageUrl,
y.name AS name
FROM
`l.p.link_y_main` AS y ) as main
This is the error:
Error: Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [16:3]
What am I doing incorrectly?
2 Answers
Answer:
1) For Big Query have to use UNION ALL instead of UNION
2) Datatypes for all union columns should be the same. -
- I had an issue that one on the columns was
STRING-INT64, INT64, INT64, STRING - So I change the rest columns to
STRINGas well, to make it work.
From documentation: UNION { ALL | DISTINCT }. So you have to use either UNION ALL or UNION DISTINCT (which is exactly what the error message says). As per HoneyBadger user mentioned this is the correct explanation.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
