amazon athena - select from multiples tables without join

Question

I have three tables. Each table has common columns with the two other tables, with difference in some attributes. As I am interested in the common attribute, I would like to make one single request to get data from all three tables.

I need to get the items from all three tables by UNION. Is it possible in AWS Athena?

ex:

table1 (att1, att2, att3)
table2 (att1, att2, att_3)
table3 (att1, att2, att3, att4)

Goal: get items from table1, table2, table3 without join in the same request.

Answer 1

This will return all values in columns for three tables (including fourth column from table3. If you only need att1, att2 just omit other columns and type only these in SELECT statements.

SELECT att1, att2, att3, NULL as att4 FROM table1
UNION ALL
SELECT att1, att2, att_3, NULL FROM table2
UNION ALL
SELECT att1, att2, att3, att4 FROM table3

UNION ALL returns duplicate values if there are any coming from different tables while UNION applies DISTINCT which means to return unique value set.

If you are looking for a solution to return common columns (by name) from all tables that you mention then you have options:

1. Do it like above, which means specify it explicitly
2. You would have to write a procedure to execute dynamic statement which would look for matching columns among given tables

Answer 2

In AWS Athena, you can use UNION operator to merge the results of two (or more) separate query expressions. For instance:

SELECT att1, att2, att3 FROM table1
UNION
SELECT att1, att2, att3 FROM table2
UNION
SELECT att1, att2, att3 FROM table3

I assume each SELECT statement within UNION has the same number of columns with the same data types in the same order.