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

Resolution

Use one of the following methods to use the results of an Athena query in another query:

• CREATE TABLE AS SELECT (CTAS): A CTAS query creates a new table from the results of a SELECT statement in another query. CTAS is useful for transforming data that you want to query regularly. CTAS has some limitations. For example, you can specify a maximum of 100 new partitions. For more information, see Considerations and limitations for CTAS queries. For examples, see Examples of CTAS queries.
• Create a view: Views are useful for querying the results of small-to-medium size queries that are specific and not expected to change. For more information, see Working with views.
• Use the WITH clause to run multiple select statements at the same time: Use the WITH clause to define one or more subqueries. Each subquery defines a temporary table, similar to a view definition. Use WITH clause subqueries to efficiently define tables that you can use when the query runs. For more information, see Parameters. Example:

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?