SELECT

Returns the result of evaluating the expressions specified after SELECT.

Can be used in combination with other operations to achieve a different effect.

Examples

SELECT "Hello, world!";

SELECT 2 + 2;

Procedure for executing SELECT

The result of the SELECT query is computed as follows:

• the set of input tables is determined: expressions after FROM are evaluated

• is computed SAMPLE / TABLESAMPLE

• FLATTEN COLUMNS or FLATTEN BY is performed; aliases specified in FLATTEN BY become visible after this point.

• All JOIN are executed.

• columns specified in GROUP BY ... AS ... are added (or replaced) to the resulting data

• the WHERE clause is executed: all data that does not satisfy the predicate is filtered out.

• GROUP BY is performed, aggregate function values are computed.

• Filtering is performed using HAVING

• Values of window functions are computed

• Expressions in SELECT are evaluated.

• expressions in SELECT are assigned names defined by aliases.

• a top-level DISTINCT is applied to the columns obtained in this way

• All subqueries in UNION ALL are computed in the same way and combined (see PRAGMA AnsiOrderByLimitInUnionAll).

• Sorting is performed according to ORDER BY

• OFFSET and LIMIT are applied to the result.

Column order in YQL

In standard SQL, the order of columns specified in the projection (in SELECT) matters. Besides the fact that the column order must be preserved when displaying query results or when writing to a new table, some SQL constructs use this order. This applies in particular to UNION ALL and positional ORDER BY (ORDER BY ordinal).

By default, the order of columns is ignored in YQL:

• the order of columns in output tables and in query results is undefined
• The data schema of the UNION ALL result is output by column names, not by positions.

When PRAGMA OrderedColumns; is enabled, the order of columns is preserved in the query results and is derived from the order of columns in the input tables according to the following rules:

• SELECT with explicit column enumeration sets the corresponding order.

• SELECT with an asterisk (SELECT * FROM ...) inherits the order from its input.

• the order of columns after JOIN: first the columns from the left side, then from the right. If the order of either side present in the output JOIN is not defined, the order of the result columns is also not defined;

• the order of depends on the execution mode of UNION ALL.

• Column order for AS_TABLE is not defined.

Combination of queries

Results of multiple SELECT (or subqueries) can be combined using the keywords UNION and UNION ALL.

query1 UNION [ALL] query2 (UNION [ALL] query3 ...)

Union of more than two queries is interpreted as a left-associative operation, i.e.

query1 UNION query2 UNION ALL query3

is interpreted as

(query1 UNION query2) UNION ALL query3

If ORDER BY/LIMIT/DISCARD/INTO RESULT is present in the combined subqueries, the following rules apply:

• ORDER BY/LIMIT/INTO RESULT is allowed only after the last subquery
• DISCARD is allowed only before the first subquery.
• the specified operators act on the result UNION [ALL], not on the subquery
• to apply an operator to a subquery, the subquery must be enclosed in parentheses.

Accessing multiple tables in a single query

In standard SQL, UNION ALL is used to query multiple tables, which combines the results of two or more SELECT. This is not very convenient for a use case where you need to run the same query across multiple tables (for example, containing data for different dates). In YQL, for convenience, in SELECT after FROM you can specify not only a single table or subquery, but also call built-in functions that allow combining data from multiple tables.

The following functions are defined for these purposes:

CONCAT(`table1`, `table2`, `table3` VIEW view_name, ...) — combines all tables listed in the arguments.

EACH($list_of_strings) or EACH($list_of_strings VIEW view_name) — combines all tables whose names are listed in the string list. Optionally, you can pass multiple lists in separate arguments, similar to CONCAT.

RANGE(`prefix`, `min`, `max`, `suffix`, `view`): combines a range of tables. Arguments:

• prefix — directory for searching tables, specified without a trailing slash. The only required argument; if only it is specified, all tables in this directory are used.
• min, max — the next two arguments specify a range of names for including tables. The range is inclusive on both ends. If the range is not specified, all tables in the prefix directory are used. Names of tables or directories located in the directory specified in prefix are compared with the range [min, max] lexicographically, not concatenated, so it is important to specify the range without leading slashes.
• suffix — table name. Expected without a leading slash. If suffix is not specified, the arguments [min, max] specify a range of table names. If suffix is specified, the arguments [min, max] specify a range of folders in which a table with the name specified in the suffix argument exists.

LIKE(`prefix`, `pattern`, `suffix`, `view`)` и `REGEXP(`prefix`, `pattern`, `suffix`, `view`) — the pattern argument is specified in a format similar to the binary operators of the same name: LIKE and REGEXP.

FILTER(`prefix`, `callable`, `suffix`, `view`) — the callable argument must be a callable expression with signature (String)->Bool, which will be called for each table/subdirectory in the prefix directory. Only those tables for which the callable value returned true will participate in the query. As a callable value, it is most convenient to use lambda functions.

By default, schemas of all participating tables are merged according to the rules of UNION ALL. If schema merging is not desired, you can use functions with the suffix _STRICT, for example CONCAT_STRICT or RANGE_STRICT, which work exactly like the original ones but treat any discrepancy in table schemas as an error.

To specify the cluster of the merged tables, you need to specify it before the function name.

All arguments of the functions described above can be declared separately using named expressions. In this case, simple expressions are also allowed in them by implicitly calling EvaluateExpr.

The name of the source table from which each row was originally obtained can be obtained using the TablePath() function.

Examples

SELECT * FROM CONCAT(
  `table1`,
  `table2`,
  `table3`);

$indices = ListFromRange(1, 4);
$tables = ListMap($indices, ($index) -> {
    RETURN "table" || CAST($index AS String);
});
SELECT * FROM EACH($tables); -- identical to the previous example

SELECT * FROM RANGE(`my_folder`);

SELECT * FROM some_cluster.RANGE( -- The cluster can be specified before the function name
  `my_folder`,
  `from_table`,
  `to_table`);

SELECT * FROM RANGE(
  `my_folder`,
  `from_folder`,
  `to_folder`,
  `my_table`);

SELECT * FROM RANGE(
  `my_folder`,
  `from_table`,
  `to_table`,
  ``,
  `my_view`);

SELECT * FROM LIKE(
  `my_folder`,
  "2017-03-%"
);

SELECT * FROM REGEXP(
  `my_folder`,
  "2017-03-1[2-4]?"
);

$callable = ($table_name) -> {
    return $table_name > "2017-03-13";
};

SELECT * FROM FILTER(
  `my_folder`,
  $callable
);

Supported constructs in SELECT

• FROM

• FROM AS_TABLE

• FROM SELECT

• DISTINCT

• UNIQUE DISTINCT

• UNION

• WITH

• WITHOUT

• WHERE

• ORDER BY

• ASSUME ORDER BY

• LIMIT OFFSET

• SAMPLE

• TABLESAMPLE

• JOIN

• GROUP BY

• FLATTEN

• WINDOW

• VIEW secondary_index

• VIEW vector_index

• VIEW fulltext_index

• VIEW json_index