SELECT Statements
A SELECT statement can consist of the following basic clauses.
- SELECT
- INTO
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- UNION
- ORDER BY
- LIMIT
SELECT Syntax
The following syntax diagram outlines the syntax supported by the Amazon Athena adapter:
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
}
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | ANY | ALL | EXISTS | NOT EXISTS | CONTAINS | NOT CONTAINS | BETWEEN | AND | OR } [ <expression> ]
} [ { AND | OR } ... ]
Examples
- Return all columns:
SELECT * FROM [AwsDataCatalog].[sampledb].Customers
- Rename a column:
SELECT "TotalDue" AS MY_TotalDue FROM [AwsDataCatalog].[sampledb].Customers
- Cast a column's data as a different data type:
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM [AwsDataCatalog].[sampledb].Customers
- Search data:
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = '12345'
- The Amazon Athena APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, ANY, ALL, EXISTS, NOT EXISTS, CONTAINS, NOT CONTAINS, BETWEEN, AND, OR.
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = '12345';
- Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM [AwsDataCatalog].[sampledb].Customers
- Return the number of unique items matching the query criteria:
SELECT COUNT(DISTINCT TotalDue) FROM [AwsDataCatalog].[sampledb].Customers
- Return the unique items matching the query criteria:
SELECT DISTINCT TotalDue FROM [AwsDataCatalog].[sampledb].Customers
- Summarize data:
SELECT TotalDue, MAX(AnnualRevenue) FROM [AwsDataCatalog].[sampledb].Customers GROUP BY TotalDue
See Aggregate Functions for details. - Retrieve data from multiple tables.
SELECT Orders.OrderDate, Customers.ContactName FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
See JOIN Queries for details. - Sort a result set in ascending order:
SELECT Name, TotalDue FROM [AwsDataCatalog].[sampledb].Customers ORDER BY TotalDue ASC