CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenapresto
Hayat Bellafkih
Hayat Bellafkih

Reputation: 599

amazon athena - select from multiples tables without join

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.

Upvotes: 3

Views: 23355

Answers (3)

mohd Bilal
mohd Bilal

Reputation: 81

SELECT att1, att2, att3 FROM table1

union

SELECT att1, att2, att3 FROM table2

union

SELECT att1, att2, att3 FROM table3

where att1, att2, att3 should have same data type for these tables

it will return the duplicate value from

SELECT att1, att2, att3 FROM table1

union all

SELECT att1, att2, att3 FROM table2

union all

SELECT att1, att2, att3 FROM table3

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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

Upvotes: 7

kahveci
kahveci

Reputation: 1457

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.

Upvotes: 2

Related Questions