Retrieves rows from zero or more tables.
[ WITH with_query [, ...] ] SELECT [ ALL | DISTINCT ] select_expression [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ] [ HAVING condition ] [ UNION [ ALL | DISTINCT ] union_query ] [ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ] [ LIMIT [ count | ALL ] ]
- [ WITH with_query [, ....] ]
SELECTlist in a query and defines one or more subqueries for use within the
SELECTquery. Each subquery defines a temporary table, similar to a view definition, which can be referenced in the
FROMclause. These tables are used only during the execution of the query. You can use
WITHto flatten nested queries or to simplify subqueries. Using
WITHto create recursive queries is not supported.
WITHworks with multiple subqueries and the relations within the
WITHclause can chain.
subquery_table_name [ ( column_name [, ...] ) ] AS (subquery)
subquery_table_nameis a unique name for a temporary table that defines the results of the
WITHclause subquery. Each
subquerymust have a table name that can be referenced in the
column_name [, ...]is an optional list of output column names. The number of column names must be equal to or less than the number of columns defined by
subqueryis any query statement
- [ ALL | DISTINCT ] select_expr
select_exprdetermines the rows to be selected. Use
DISTINCTto return only distinct values when a column contains duplicate values.
ALLis the default. Using
ALLis treated the same as if it were omitted; all rows for all columns are selected and duplicates are kept.
- FROM from_item [, ...]
Indicates the input to the query, where
from_itemcan be a view, a join construct, or a subquery as described below.
from_itemcan be either:
table_name [ [ AS ] alias [ (column_alias [, ...]) ] ]
table_nameis the name of the target table from which to select rows,
aliasis the name to give the output of the
column_aliasdefines the columns for the
join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
join_typeis one of:
[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
ON join_condition | USING (join_column [, ...])Where using
join_conditionallows you to specify column names for join keys in multiple tables, and using
join_columnto exist in both tables.
- [ WHERE condition ]
- Filters results according to the
- [ GROUP BY [ ALL | DISTINCT ] grouping_expressions [, ...] ]
- Divides the output of the
SELECTstatement into rows with matching values.
DISTINCTdetermine whether duplicate grouping sets each produce distinct output rows. If omitted,
ALLis assumed. The
grouping_expressionselement can be any function (such as
COUNT, etc.) performed on input columns or be an ordinal number that selects an output column by position, starting at one.
GROUP BYexpressions can group output by input column names that don't appear in the output of the
SELECTstatment. All output expressions must be either aggregate functions or columns present in the
GROUP BYclause. ''grouping_expressions`` allow you to perform complex grouping operations. You can use a single query to perform analysis that requires aggregating multiple column sets. These complex grouping operations don't support expressions comprising input columns. Only column names or ordinals are allowed. You can often use
UNION ALLto achieve the same results as these
GROUP BYoperations, but queries that use
GROUP BYhave the advantage of reading the data once, whereas
UNION ALLreads the underlying data three times and may produce inconsistent results when the data source is subject to change.
GROUP BY CUBEgenerates all possible grouping sets for a given set of columns.
GROUP BY ROLLUPgenerates all possible subtotals for a given set of columns.
- [ HAVING condition ]
- Used with aggregate functions and the
GROUP BYclause. Controls which groups are selected, eliminating groups that don't satisfy
condition. This filtering occurs after groups and aggregates are computed.
- [ UNION [ ALL | DISTINCT ] union_query] ]
- Combines the results of more than one
SELECTstatement into a single query.
DISTINCTcontrol which rows are included in the final result set.
ALLcauses all rows to be included, even if the rows are identical, while
DISTINCTcauses only unique rows to be included in the combined result set.
DISTINCTis the default. Multiple
UNIONclauses are processed left to right unless you use parentheses to explictly define the order of processing.
- [ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
- Sorts a result set by one or more output
expression. When the clause contains multiple expressions, the result set is sorted according to the first
expression. Then the second
expressionis applied to rows that have matching values from the first expression, and so on. Each
expressionmay specify output columns from
SELECTor an ordinal number for an output column by position, starting at one.
ORDER BYis evaluted as the last step after any
DESCdetermine whether results are sorted in ascending or descending order. The default null ordering is
NULLS LAST, regardless of ascending or descending sort order.
- LIMIT [ count | ALL ]
- Restricts the number of rows in the result set to
LIMIT ALLis the same as omitting the
LIMITclause. If the query has no
ORDER BYclause, the results are arbitrary.
- TABLESAMPLE BERNOULLI | SYSTEM (percentage)
- Optional operator to select rows from a table based on a sampling
BERNOULLIselects each row to be in the table sample with a probability of
percentage. All physical blocks of the table are scanned, and certain rows are skipped based on a comparison between the sample
percentageand a random value calculated at runtime. With
SYSTEM, the table is divided into logical segments of data, and the table is sampled at this granulariy. Either all rows from a particular segment are selected, or the segment is skipped based on a comparison between the sample
percentageand a random value calculated at runtime.
SYTSTEMsampling is dependent on the connector. This method does not guarantee independent sampling probabilities.
- [ UNNEST (array_or_map) [WITH ORDINALITY] ]
- Expands an array or map into a relation. Arrays are expanded into a
single column. Maps are expanded into two columns (key, value). You
UNNESTwith multiple arguments, which are expanded into multiple columns with as many rows as the highest cardinality argument. Other columns are padded with nulls. The
WITH ORDINALITYclause adds an ordinality column to the end.
UNNESTis usually used with a
JOINand can reference columns from relations on the left side of the
SELECT * from table;
SELECT os, COUNT(*) count FROM cloudfront_logs WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05' GROUP BY os;