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:

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

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

Previous
Next