List the columns that specific tables have in common - Amazon Athena
Services or capabilities described in AWS documentation might vary by Region. To see the differences applicable to the AWS European Sovereign Cloud Region, see the AWS European Sovereign Cloud User Guide.

List the columns that specific tables have in common

You can list the columns that specific tables in a database have in common.

  • Use the syntax SELECT column_name FROM information_schema.columns.

  • For the WHERE clause, use the syntax WHERE table_name IN ('table1', 'table2').

Example– Listing common columns for two tables in the same database

The following example query lists the columns that the tables table1 and table2 have in common.

SELECT column_name
FROM information_schema.columns
WHERE table_name IN ('table1', 'table2')
GROUP BY column_name
HAVING COUNT(*) > 1;