ydb/yql/essentials/docs/en/syntax/select/index.md

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 {#selectexec}

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 BY become 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 {#orderedcolumns}

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 ALL result 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.
• SELECT with 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 JOIN output is undefined, the column order in the result is also undefined.
• The order in UNION ALL depends on the UNION ALL execution mode.
• The column order for AS_TABLE is undefined.

Combining queries {#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 RESULT is only allowed after the last query
• DISCARD is 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

Clauses supported in SELECT

• FROM
• FROM AS_TABLE
• FROM SELECT
• DISTINCT
• UNIQUE DISTINCT
• UNION
• WITH
• WITHOUT
• WHERE
• ORDER BY
• ASSUME ORDER BY
• LIMIT OFFSET
• SAMPLE
• TABLESAMPLE
• CONCAT