How do I use the results of an Amazon Athena query in another query?

Short description

To use the results of an Athena query in another query, choose one of the following methods:

• Create a new table from the results with a CREATE TABLE AS SELECT (CTAS) query
• Create a view
• Use a WITH clause to run multiple SELECT statements in the same query

Resolution

Create a new table from the Athena query results with a CTAS query

A CTAS query creates a new table from the results of a SELECT statement in another query. CTAS queries are useful when you want to transform data that you regularly query. For examples of CTAS queries, see Examples of CTAS queries.

Keep in mind that CTAS queries do have some limitations. For example, you can specify a maximum of only 100 new partitions. For more information, see Considerations and limitations for CTAS queries.

Create a view

Views are useful when you want to query the results of small-to-medium size queries that are specific and not expected to change. For more information, see Working with views.

Use a WITH clause to run multiple SELECT statements in the same query

Use a WITH clause to define one or more subqueries. Each subquery defines a temporary table, similar to a view definition. WITH clause subqueries are useful to efficiently define tables that you can use when the query runs. For more information, see Parameters.

For example, the following query contains a subquery within a WITH clause:

WITH temp AS (SELECT * FROM tbl1 WHERE col1 = 1) SELECT * FROM tbl2, temp;

Related information

How can I access and download the results of an Amazon Athena query?

Reusing query results