BigQuery , Union issue / Error: Syntax error: Expected keyword ALL or keyword DISTINCT but got keywo

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?

5

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 STRING as 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.

    1

    ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobGlxaG6CeoCOm6CgqaWav7p51KegqKZdnsC0wcRmnKuqn6d6tMXNrZixZZWnv7C%2BjJ6vqZ2TqbKlecqesLCnopl6orjLZqarZZuaxri70Z1knaE%3D