---
metadata:
  - name: generator
    content: Diplodoc Platform v5.50.6
alternate:
  - https://ydb.tech/docs/en/yql/reference/syntax/select.md?version=main
  - https://ydb.tech/docs/ru/yql/reference/syntax/select.md?version=main
sourcePath: en/core/yql/reference/syntax/select/index.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

## 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


```yql
SELECT "Hello, world!";
```


```yql
SELECT 2 + 2;
```


## Procedure for executing SELECT {#selectexec}

The result of the `SELECT` query is computed as follows:

* the set of input tables is determined: expressions after [FROM](https://ydb.tech/docs/en/yql/reference/syntax/select/from.md?version=main) are evaluated


* is computed [SAMPLE](https://ydb.tech/docs/en/yql/reference/syntax/select/sample.md?version=main) / [TABLESAMPLE](https://ydb.tech/docs/en/yql/reference/syntax/select/sample.md?version=main)
* [FLATTEN COLUMNS](https://ydb.tech/docs/en/yql/reference/syntax/select/flatten.md?version=main#flatten-columns) or [FLATTEN BY](https://ydb.tech/docs/en/yql/reference/syntax/select/flatten.md?version=main) is performed; aliases specified in `FLATTEN BY` become visible after this point.


* All [JOIN](https://ydb.tech/docs/en/yql/reference/syntax/select/join.md?version=main) are executed.


* columns specified in [GROUP BY ... AS ...](https://ydb.tech/docs/en/yql/reference/syntax/select/group-by.md?version=main) are added (or replaced) to the resulting data
* the [WHERE](https://ydb.tech/docs/en/yql/reference/syntax/select/where.md?version=main) clause is executed: all data that does not satisfy the predicate is filtered out.
* [GROUP BY](https://ydb.tech/docs/en/yql/reference/syntax/select/group-by.md?version=main) is performed, aggregate function values are computed.
* Filtering is performed using [HAVING](https://ydb.tech/docs/en/yql/reference/syntax/select/group-by.md?version=main#having)


* Values of [window functions](https://ydb.tech/docs/en/yql/reference/syntax/select/window.md?version=main) are computed


* Expressions in `SELECT` are evaluated.
* expressions in `SELECT` are assigned names defined by aliases.
* a top-level [DISTINCT](https://ydb.tech/docs/en/yql/reference/syntax/select/distinct.md?version=main) is applied to the columns obtained in this way
* All subqueries in [UNION ALL](https://ydb.tech/docs/en/yql/reference/syntax/select/union.md?version=main#union-all) are computed in the same way and combined (see [PRAGMA AnsiOrderByLimitInUnionAll](https://ydb.tech/docs/en/yql/reference/syntax/pragma.md?version=main#pragmas)).
* Sorting is performed according to [ORDER BY](https://ydb.tech/docs/en/yql/reference/syntax/select/order_by.md?version=main)
* [OFFSET and LIMIT](https://ydb.tech/docs/en/yql/reference/syntax/select/limit_offset.md?version=main) are applied to the result.

## Column order in YQL {#orderedcolumns}

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](https://ydb.tech/docs/en/yql/reference/syntax/select/union.md?version=main#union-all) and positional [ORDER BY](https://ydb.tech/docs/en/yql/reference/syntax/select/order_by.md?version=main) (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](https://ydb.tech/docs/en/yql/reference/syntax/select/join.md?version=main): 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`](https://ydb.tech/docs/en/yql/reference/syntax/select/union.md?version=main#union-all).
* Column order for [AS_TABLE](https://ydb.tech/docs/en/yql/reference/syntax/select/from_as_table.md?version=main) is not defined.

## Combination of queries {#combining-queries}

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


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


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


```yql
query1 UNION query2 UNION ALL query3
```


is interpreted as


```yql
(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](https://ydb.tech/docs/en/yql/reference/syntax/select/union.md?version=main#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](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#like) and [REGEXP](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#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](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#lambda).

{% note warning %}

The order in which tables are merged by all the above functions is not guaranteed.

The list of tables is computed **before** the query itself is executed. Therefore, tables created during the query will not be included in the function results.

{% endnote %}

By default, schemas of all participating tables are merged according to the rules of [UNION ALL](https://ydb.tech/docs/en/yql/reference/syntax/select/union.md?version=main#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](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#named-nodes). In this case, simple expressions are also allowed in them by implicitly calling [EvaluateExpr](https://ydb.tech/docs/en/yql/reference/builtins/basic.md?version=main#evaluate_expr_atom).

The name of the source table from which each row was originally obtained can be obtained using the [TablePath()](https://ydb.tech/docs/en/yql/reference/builtins/basic.md?version=main#tablepath) function.

### Examples


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


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


```yql
SELECT * FROM RANGE(`my_folder`);
```


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


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


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


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


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


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

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


## Supported constructs in SELECT

* [FROM](https://ydb.tech/docs/en/yql/reference/syntax/select/from.md?version=main)
* [FROM AS_TABLE](https://ydb.tech/docs/en/yql/reference/syntax/select/from_as_table.md?version=main)
* [FROM SELECT](https://ydb.tech/docs/en/yql/reference/syntax/select/from_select.md?version=main)
* [DISTINCT](https://ydb.tech/docs/en/yql/reference/syntax/select/distinct.md?version=main)
* [UNIQUE DISTINCT](https://ydb.tech/docs/en/yql/reference/syntax/select/unique_distinct_hints.md?version=main)
* [UNION](https://ydb.tech/docs/en/yql/reference/syntax/select/union.md?version=main)
* WITH
* [WITHOUT](https://ydb.tech/docs/en/yql/reference/syntax/select/without.md?version=main)
* [WHERE](https://ydb.tech/docs/en/yql/reference/syntax/select/where.md?version=main)
* [ORDER BY](https://ydb.tech/docs/en/yql/reference/syntax/select/order_by.md?version=main)
* [ASSUME ORDER BY](https://ydb.tech/docs/en/yql/reference/syntax/select/assume_order_by.md?version=main)
* [LIMIT OFFSET](https://ydb.tech/docs/en/yql/reference/syntax/select/limit_offset.md?version=main)
* [SAMPLE](https://ydb.tech/docs/en/yql/reference/syntax/select/sample.md?version=main)
* [TABLESAMPLE](https://ydb.tech/docs/en/yql/reference/syntax/select/sample.md?version=main)



* [JOIN](https://ydb.tech/docs/en/yql/reference/syntax/select/join.md?version=main)


* [GROUP BY](https://ydb.tech/docs/en/yql/reference/syntax/select/group-by.md?version=main)
* [FLATTEN](https://ydb.tech/docs/en/yql/reference/syntax/select/flatten.md?version=main)


* [WINDOW](https://ydb.tech/docs/en/yql/reference/syntax/select/window.md?version=main)







* [VIEW secondary_index](https://ydb.tech/docs/en/yql/reference/syntax/select/secondary_index.md?version=main)
* [VIEW vector_index](https://ydb.tech/docs/en/yql/reference/syntax/select/vector_index.md?version=main)
* [VIEW fulltext_index](https://ydb.tech/docs/en/yql/reference/syntax/select/fulltext_index.md?version=main)
* [VIEW json_index](https://ydb.tech/docs/en/yql/reference/syntax/select/json_index.md?version=main)