SELECT
Returns the result of evaluating the expressions specified after SELECT.
It can be used in combination with other operations to obtain other effect.
Examples
SELECT "Hello, world!";
SELECT 2 + 2;
SELECT execution procedure
The SELECT query result is calculated as follows:
-
Determine the set of input tables by evaluating the FROM clauses.
-
Apply SAMPLE/TABLESAMPLE to input tables.
-
Execute FLATTEN COLUMNS or FLATTEN BY; aliases set in
FLATTEN BYbecome visible after this point. -
Execute every JOIN.
-
Add to (or replace in) the data the columns listed in GROUP BY ... AS ....
-
Execute WHERE — Discard all the data mismatching the predicate.
-
Execute GROUP BY, evaluate aggregate functions.
-
Apply the filter HAVING.
-
Evaluate window functions;
-
Evaluate expressions in
SELECT. -
Assign names set by aliases to expressions in
SELECT. -
Apply top-level DISTINCT to the resulting columns.
-
Execute similarly every subquery inside UNION ALL, combine them (see PRAGMA AnsiOrderByLimitInUnionAll).
-
Perform sorting with ORDER BY.
-
Apply OFFSET and LIMIT to the result.
Column order in YQL
The standard SQL is sensitive to the order of columns in projections (that is, in SELECT). While the order of columns must be preserved in the query results or when writing data to a new table, some SQL constructs use this order.
This applies, for example, to UNION ALL and positional ORDER BY (ORDER BY ordinal).
The column order is ignored in YQL by default:
- The order of columns in the output tables and query results is undefined
- The data scheme of the
UNION ALLresult is output by column names rather than positions
If you enable PRAGMA OrderedColumns;, the order of columns is preserved in the query results and is derived from the order of columns in the input tables using the following rules:
-
SELECT: an explicit column enumeration dictates the result order. -
SELECTwith an asterisk (SELECT * FROM ...) inherits the order from its input. -
The order of columns after JOIN: First output the left-hand columns, then the right-hand ones. If the column order in any of the sides in the
JOINoutput is undefined, the column order in the result is also undefined. -
The order in
UNION ALLdepends on the UNION ALL execution mode. -
The column order for AS_TABLE is undefined.
Warning
In the YT table schema, key columns always precede non-key columns. The order of key columns is determined by the order of the composite key.
When PRAGMA OrderedColumns; is enabled, non-key columns preserve their output order.
Combining queries
Results of several SELECT statements (or subqueries) can be combined using UNION and UNION ALL keywords.
query1 UNION [ALL] query2 (UNION [ALL] query3 ...)
Union of more than two queries is interpreted as a left-associative operation, that is
query1 UNION query2 UNION ALL query3
is interpreted as
(query1 UNION query2) UNION ALL query3
If the underlying queries have one of the ORDER BY/LIMIT/DISCARD/INTO RESULT operators, the following rules apply:
ORDER BY/LIMIT/INTO RESULTis only allowed after the last queryDISCARDis only allowed before the first query- the operators apply to the
UNION [ALL]as a whole, instead of referring to one of the queries - to apply the operator to one of the queries, enclose the query in parantheses