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?
5Answer:
1) For Big Query have to use UNION ALL
instead of UNION
2) Datatypes for all union columns should be the same. -
STRING
- INT64, INT64, INT64, STRING
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.
ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobGlxaG6CeoCOm6CgqaWav7p51KegqKZdnsC0wcRmnKuqn6d6tMXNrZixZZWnv7C%2BjJ6vqZ2TqbKlecqesLCnopl6orjLZqarZZuaxri70Z1knaE%3D