SELECT executes data retrieval. By default, the requested data is returned to the client, and combined with INSERT INTO, it can be forwarded to different tables.

Syntax overview #

[WITH expr_list|(subquery)] 
SELECT [DISTINCT] expr_list 
[FROM [db.]table | (subquery) | table_function] [FINAL] 
[SAMPLE sample_coeff] 
[ARRAY JOIN ...] 
[ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>) 
[PREWHERE expr] 
[WHERE expr] 
[GROUP BY expr_list] [WITH TOTALS] 
[HAVING expr] 
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] 
[LIMIT [offset_value, ]n BY columns] 
[LIMIT [n, ]m] [WITH TIES] 
[UNION ALL ...] 
[settingsClause] 
[INTO OUTFILE filename] 
[FORMAT format]

All clauses are optional, but the required expression list immediately following SELECT is not.

SELECT clause #

SELECT [DISTINCT] expr_list

DISTINCT retains only unique rows in the query results. It treats NULL as a specific value, and NULL==NULL.
Asterisk symbol (*): the asterisk can be placed in any part of the query to replace an expression. When analyzing the query, the asterisk expands to a list of all table columns (excluding MATERIALIZED and ALIAS columns).
You can use aliases (AS) anywhere in the query.

Use cases

• To include all columns in the result, use the asterisk (*) symbol. For example, SELECT * FROM ....

• To include columns that contain 'a' in the result, you can use the COLUMNS expression.

  Example:

CREATE TABLE sample.col_names (aa Int8, ab Int8, bc Int8) ENGINE = CnchMergeTree order by aa;

The following query selects all column names that contain a.

SELECT COLUMNS('a') FROM sample.col_names

┌─aa─┬─ab─┐ 
│  1 │  1 │ 
└────┴────┘

• Use multiple COLUMNS expressions and apply functions to them.

  Example

SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM sample.col_names

┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐ 
│  1 │  1 │  1 │ Int8           │ 
└────┴────┴────┴────────────────┘

Each column returned by the COLUMNS expression is passed as a separate parameter to the function. If the function supports other parameters, you can also pass other parameters to the function.

For example:

SELECT COLUMNS('a') + COLUMNS('c') FROM sample.col_names

Code: 42, Error: DB::Exception: Number of arguments for function plus doesn't match: passed 3, should be 2 SQLSTATE: 42000

In this example, COLUMNS('a') returns two columns: aa and ab. COLUMNS('c') returns the bc column. The + operator cannot be applied to three parameters, so ByteHouse throws an exception with a relevant message.
Matched columns COLUMNS expressions can have different data types. If COLUMNS does not match any columns and is the only expression in SELECT, ClickHouse throws an exception.

Asterisk

You can use the asterisk to replace expressions in any part of the query. When analyzing the query, the asterisk expands to all table columns (excluding MATERIALIZED and ALIAS columns). There are only a few reasonable scenarios for using the asterisk:

• When creating dump tables.

• For tables that contain only a few columns, such as system tables.

• To obtain information about columns in a table. In this case, set LIMIT 1. But it is better to use the DESC TABLE query.

• When using PREWHERE for strong filtering on a small number of columns.

• In subqueries (because columns not required by the outer query are excluded from the subquery).

  In all other cases, we do not recommend using the asterisk, as it only exposes a disadvantage of column-oriented DBMSs rather than an advantage. In other words, using the asterisk is not recommended.

Implementation details

If the query omits DISTINCT, GROUP BY, ORDER BY, IN, or JOIN subqueries, the query will be fully processed in a streaming manner, using O(1) RAM. If appropriate limits are not specified, the query may consume a large amount of RAM:

• max_memory_usage
• max_rows_to_group_by
• max_rows_to_sort
• max_rows_in_distinct
• max_bytes_in_distinct
• max_rows_in_set
• max_bytes_in_set
• max_rows_in_join
• max_bytes_in_join
• max_bytes_before_external_sort
• max_bytes_before_external_group_by

WITH clause #

This section provides support for common table expressions (CTEs), so the result of the WITH clause can be used in the remaining SELECT queries.
ByteHouse supports common table expressions (CTEs). The result of the WITH clause can be used in subsequent SELECT queries. There are certain restrictions on support, including:

• Recursion is not allowed in CTEs

• Subqueries are not allowed in CTEs

  Syntax

WITH [columnExpr] AS identifier

Example 1: Using a constant expression as a "variable"

WITH '2024-07-01 15:23:00' as ts_upper_bound 
SELECT * 
FROM system.tables 
WHERE 
metadata_modification_time <= ts_upper_bound

Example 2: Extracting the result of the sum(bytes) expression from the SELECT clause list

WITH sum(total_bytes) as s 
SELECT 
    formatReadableSize(s), 
    database 
FROM system.tables 
GROUP BY database 
ORDER BY s

Example 3: Using the result of a scalar subquery

/* this example would return TOP 10 of most huge database */ 
WITH 
    ( 
        SELECT sum(total_bytes) 
        FROM system.tables 
    ) AS total_resoure_size 
SELECT 
    (sum(total_bytes) / total_resoure_size) * 100 AS db_resource_usage, 
    database 
FROM system.tables 
GROUP BY database 
ORDER BY db_resource_usage DESC 
LIMIT 10

Example 4: Reusing expressions in subqueries

WITH ['hello'] AS hello 
SELECT 
    hello, 
    * 
FROM 
( 
    WITH ['hello'] AS hello 
    SELECT hello 
) 

--result 

┌─hello─────┬─hello_1─────┐ 
│ ['hello'] │ ['hello'] │ 
└───────────┴───────────┘

FROM clause #

The fromClause specifies the source of the data to be read:

• Table

• Subquery

• Table function

  Syntax
  Read data from a table

FROM [tableIdentifier] [FINAL] [sampleClause]

Read data from a subquery

FROM ([selectUnionStmt]) [FINAL] [sampleClause]

Read data from a table function

FROM tableFunctionExpr [FINAL] [sampleClause]

• FINAL: When FINAL is specified, ByteHouse fully merges the data before returning the result, so all data transformations are performed during the merge process.

  Example

1. Querying from a table

CREATE TABLE test.fromClause (id UInt32, val UInt32) ENGINE=CnchMergeTree() ORDER BY id; 
INSERT INTO test.fromClause VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); 
SELECT * FROM test.fromClause; -- expect 5 rows

1. Querying from a subquery

CREATE TABLE test.fromClause (id UInt32, val UInt32) ENGINE=CnchMergeTree() ORDER BY id; 
INSERT INTO test.fromClause VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); 
SELECT * FROM (SELECT * FROM test.fromClause LIMIT 3); -- expect 3 rows

1. Querying from a table function

SELECT * FROM numbers(10); -- expect 10 rows

Here, we use the table function numbers to generate a table containing 10 rows.

SAMPLE clause #

The sampleClause allows approximate SELECT query processing. When the sampleClause is enabled, the query is executed only on a subset of the data, not on all data. This is very useful in the following situations:

• There are strict time requirements (such as less than 100 milliseconds), but it cannot be justified to increase hardware resources to meet these requirements.

• The original data is not accurate, so approximation does not significantly reduce quality.

• The business requirement goal is to obtain approximate results (for cost efficiency, or to provide precise results to advanced users).

  Caution: To use sampling, you must declare the sampling expression when creating a CnchMergeTree table. For details, see [sampleByClause].
  The functions of data sampling are listed below:

• Data sampling is a deterministic mechanism. The same result SELECT .. SAMPLE queries always return the same result.

• Sampling works consistently across different tables. For tables with a single sampling key, sampling with the same coefficient always selects the same possible subset of data. For example, sampling by User ID selects rows from the same subset of all possible User IDs, regardless of the table. This means you can use sampling in subqueries, and you can also use JOIN.

• Sampling allows less data to be read from disk. Please note that you must specify the sampling key correctly.

  The following syntax is supported for the SAMPLE clause:

| | | \

SAMPLE K

Here, k is a number from 0 to 1 (supports decimal and fractional notation). For example, SAMPLE 1/2 or SAMPLE 0.5.
Syntax

SAMPLE K

Example
In this example, 10% of the data is used for approximate calculation.

CREATE TABLE IF NOT EXISTS test.sampleClause (id UInt32) ENGINE=CnchMergeTree ORDER BY id SAMPLE BY id; 
INSERT INTO test.sampleClause SELECT * FROM numbers(1000); 
SELECT COUNT() FROM test.sampleClause SAMPLE 0.1; -- 1000 is expected

SAMPLE N

Here, n is a sufficiently large integer. For example, SAMPLE 10000000.
In this case, the query is executed on at least n rows (but not more than this). For example, SAMPLE 10000000 executes the query on at least 10,000,000 rows.
Syntax

SAMPLE N

Example
In this example, 2 rows of data are used for approximate calculation.

CREATE TABLE IF NOT EXISTS test.sampleClause (id UInt32) ENGINE=CnchMergeTree ORDER BY id SAMPLE BY id; 
INSERT INTO test.sampleClause SELECT * FROM numbers(1000); 
SELECT COUNT() FROM test.sampleClause SAMPLE 2; -- 1000 is expected

SAMPLE K OFFSET M

Here, k is a number from 0 to 1 (supports decimal and fractional notation). For example, SAMPLE 1/2 or SAMPLE 0.5.
Syntax

SAMPLE K OFFSET M

Example
In this example, after skipping 20% of the data, 10% of the data is used for approximate calculation.

CREATE TABLE IF NOT EXISTS test.sampleClause (id UInt32) ENGINE=CnchMergeTree ORDER BY id SAMPLE BY id; 
INSERT INTO test.sampleClause SELECT * FROM numbers(1000); 
SELECT COUNT() FROM test.sampleClause SAMPLE 0.1 OFFSET 0.2;

JOIN clause #

Join combines columns from one or more tables into a new table by using values shared by each table.
Syntax

[ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER] JOIN [tableIdentifier] ON|USING [columnExprList]

[ANY|ALL|ASOF]：

• ANY: If there are multiple matching rows in the right table, only the first matching row found is joined. If there is only one matching row in the right table, the results of ANY and ALL are the same.

• ALL: If there are multiple matching rows in the right table, ClickHouse creates a Cartesian product from the matching rows. This is the normal JOIN behaviour in standard SQL.

• ASOF: Used to join sequences with uncertain matches.

  [INNER|LEFT|RIGHT|FULL|CROSS]: All standard SQL JOIN types.

• INNER JOIN: Only matching rows are returned.

• LEFT OUTER JOIN: In addition to returning matching rows, rows from the left table that do not match are also returned.

• RIGHT OUTER JOIN: In addition to returning matching rows, rows from the right table that do not match are also returned.

• FULL OUTER JOIN: In addition to returning matching rows, rows from both tables that do not match are also returned.

• CROSS JOIN: Generates a Cartesian product of the entire table without specifying a "join key".

  ON|USING：

• Expressions in the ON clause and columns in the USING clause are called "join keys".

  Example

CREATE TABLE IF NOT EXISTS test.joinClause (number UInt64) ENGINE=CnchMergeTree ORDER BY number; 
INSERT INTO test.joinClause SELECT * FROM numbers(10); 

SELECT number, joined FROM test.joinClause ANY LEFT JOIN (SELECT number * 2 AS number, number * 10 + 1 AS joined FROM test.joinClause LIMIT 10) js2 USING number LIMIT 10

| | | \

ARRAY JOIN clause #

For tables that contain array columns, array join can generate a new table whose columns contain each individual element from the original array column, while values in other columns are repeated.
Syntax

[LEFT] ARRAY JOIN [columnExprList]

Only one arrayJoinClause can be specified in a SELECT query.
[LEFT] ARRAY JOIN: ARRAY JOIN type

• ARRAY JOIN - By default, empty arrays are not included in the result of JOIN.

• LEFT ARRAY JOIN - The result of JOIN includes rows with empty arrays. The values of empty arrays are set to the default value of the array element type (usually 0, empty string, or NULL).

  Example

CREATE TABLE test.arrayJoinClause(s String, arr Array(UInt8)) ENGINE = CnchMergeTree ORDER BY s; 
INSERT INTO test.arrayJoinClause VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []); 
SELECT s, arr FROM test.arrayJoinClause ARRAY JOIN arr;

PREWHERE clause #

Prewhere is an optimization used to apply filtering more efficiently. Even if the PREWHERE clause is not explicitly specified, it is activated by default. It works by automatically moving part of the WHERE condition to the prewhere stage. The purpose of the PREWHERE clause is only to control this optimization when you believe you can do better than the default optimization.
When using prewhere optimization, only the columns required to run the prewhere expression are read first. Then, other columns required to run the rest of the query are read, but only from data blocks where the prewhere expression is "true" for certain rows. If there are many data blocks where the prewhere expression evaluates to "false" for all rows, and prewhere requires fewer columns than other parts of the query, this can often significantly reduce the amount of data read from disk during query execution.
Syntax

PREWHERE [columnExpr](http://columnexpr/)

Example

CREATE TABLE test.prewhereClause(id UInt32, val UInt32) ENGINE = CnchMergeTree() ORDER BY id; 
INSERT INTO test.prewhereClause VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); 
SELECT * FROM test.prewhereClause PREWHERE id=1 WHERE val>10; -- (1, 11), (1, 12) expected

Where clause #

The WHERE clause allows filtering data from the FROM clause of SELECT.
If there is a WHERE clause, it must contain an expression of UInt8 type. This is usually an expression with comparison and logical operators. Rows where the expression result is 0 will be excluded from further transformation or results.
If the underlying table engine supports it, the WHERE expression will use indexes and partitions for pruning.
Syntax

WHERE [columnExpr]

Example

CREATE TABLE test.whereClause(id UInt32, val UInt32) ENGINE = CnchMergeTree() ORDER BY id; 
INSERT INTO test.whereClause VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); 
SELECT * FROM test.whereClause WHERE val>10; -- (1, 11), (1, 12), (2, 20), (2, 21) expected

To test a NULL value, use the IS NULL and IS NOT NULL operators or the isNull and isNotNull functions. Otherwise, expressions containing NULL will never pass.
Example

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=CnchMergeTree() ORDER BY x; 
INSERT INTO t_null VALUES (1, NULL), (2, 3); 

SELECT * FROM t_null WHERE y IS NULL; 
SELECT * FROM t_null WHERE y != 0;

Result

┌─x─┬────y─┐ 
│ 1 │ ᴺᵁᴸᴸ │ 
└───┴──────┘ 
┌─x─┬─y─┐ 
│ 2 │ 3 │ 
└───┴───┘

Group by clause #

The GROUP BY clause converts SELECT query results to aggregate mode. The [columnExprList] under the GROUP BY clause serves as the grouping key. The aggregate SELECT query results will contain the same number of rows as the number of unique grouping key values in the source table.
Syntax

GROUP BY [(][columnExprList][)] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]

• NULL processing: ByteHouse interprets NULL as a value. If the grouping key contains NULL values, they will appear in the results.

• [WITH ROLLUP]: Subtotals are calculated according to the order of key expressions in the GROUP BY list.

  • Subtotal rows are added to the result table afterwards.
  • In subtotal rows, the values of already "grouped" key expressions are set to 0 or empty rows.

• [WITH CUBE]: Subtotals are calculated for every combination of key expressions in the GROUP BY list.

• [WITH TOTALS]: Subtotals are calculated for all combinations of key expressions in the GROUP BY list.

  Example 1: Group by key contains NULL values.

CREATE TABLE IF NOT EXISTS test.groupByClause (x UInt32, y Nullable(UInt32)) ENGINE=CnchMergeTree ORDER BY x; 
INSERT INTO test.groupByClause VALUES (1,2),(2,NULL),(3,2),(3,3),(3,NULL); 
SELECT sum(x), y FROM test.groupByClause GROUP BY y;

Example 2: WITH ROLLUP modifier for group summary

CREATE TABLE IF NOT EXISTS test.groupByClause (year UInt32, month UInt32, day UInt32) ENGINE=CnchMergeTree ORDER BY year; 
INSERT INTO test.groupByClause VALUES (2019,1,5),(2019,1,15),(2020,1,5),(2020,1,15),(2021,1,5),(2021,1,15); 
SELECT year, month, day, count() FROM test.groupByClause GROUP BY year, month, day WITH ROLLUP;

Since the GROUP BY section contains three key expressions, the results include subtotal tables summarized from right to left:

• GROUP BY year, month, day；

• GROUP BY year, month (and the day column is filled with zero);

• GROUP BY year (now both month and day columns are filled with zero);

• Total (all three key expression columns are zero).

  Example 3: WITH CUBE modifier for group summary

CREATE TABLE IF NOT EXISTS test.groupByClause (year UInt32, month UInt32, day UInt32) ENGINE=CnchMergeTree ORDER BY year; 
INSERT INTO test.groupByClause VALUES (2019,1,5),(2019,1,15),(2020,1,5),(2020,1,15),(2021,1,5),(2021,1,15); 
SELECT year, month, day, count() FROM test.groupByClause GROUP BY year, month, day WITH CUBE;

Since the GROUP BY section contains three key expressions, the results include eight subtotal tables, covering subtotals for all combinations of key expressions:

• GROUP BY year, month, day

• GROUP BY year, month

• GROUP BY year, day

• GROUP BY year

• GROUP BY month, day

• GROUP BY month

• GROUP BY day

• Total (all three key expression columns are zero)

  Example 4: WITH TOTALS modifier for group summary

CREATE TABLE IF NOT EXISTS test.groupByClause (year UInt32, month UInt32, day UInt32) ENGINE=CnchMergeTree ORDER BY year; 
INSERT INTO test.groupByClause VALUES (2019,1,5),(2019,1,15),(2020,1,5),(2020,1,15),(2021,1,5),(2021,1,15); 
SELECT year, month, day, count() FROM test.groupByClause GROUP BY year, month, day WITH TOTALS;

If the WITH TOTALS modifier is specified, an additional row will be calculated.

• GROUP BY year, month, day
• Total (all three key expression columns are zero)

LIMIT BY clause #

Select the first m rows for each different expression value
Syntax
Select the first m rows from the results for each different list expression value.

LIMIT m BY [columnExprList]

Example: Limit m rows by list expression

CREATE TABLE test.limitByClause(id UInt32, val UInt32) ENGINE = CnchMergeTree() order by id; 
INSERT INTO test.limitByClause VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); 
SELECT * FROM test.limitByClause ORDER BY id, val LIMIT 2 BY id; -- expect 4 rows. (1, 10), (1, 11), (2, 20), (2, 21)

In this example, for each id (including values 1 and 2), we need to return 2 rows.

HAVING clause #

Filter the aggregate results generated by groupByClause. It is similar to whereClause, but the difference is that WHERE is executed before aggregation, while HAVING is executed after aggregation.
Syntax

HAVING [columnExpr](http://columnexpr/)

Example

CREATE TABLE test.havingClause(id UInt32, val UInt32) ENGINE = CnchMergeTree() ORDER BY id; 
INSERT INTO test.havingClause VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); 
SELECT id FROM test.havingClause GROUP BY id HAVING count(id)>2; -- only 1 is expected.

DISTINCT clause #

If SELECT DISTINCT is declared, only unique rows are retained in the query results. Therefore, only one row is retained from all sets of fully matching rows in the results.
Null handling
DISTINCT applies to NULL as if NULL is a specific value, and NULL==NULL. In other words, in the DISTINCT results, different combinations of NULL occur only once. It is different from the way NULL is handled in most other cases.
Alternative method
The same results can be acquired by applying GROUP BY with the same group values specified as the SELECT clause, and without using any aggregate functions. But there are several differences with GROUP BY:

• DISTINCT can be used together with GROUP BY.
• When ORDER BY is omitted and LIMIT is defined, the operation stops immediately after reading the required number of different rows.
• Data blocks are output during processing, without waiting for the entire query to finish.

Caution
DISTINCT is not supported when the SELECT contains columns with arrays.

Example
ByteHouse supports using DISTINCT and ORDER BY on different columns in a single query. The DISTINCT clause is executed before the ORDER BY clause.
Example table:

┌─a─┬─b─┐ 
│ 2 │ 1 │ 
│ 1 │ 2 │ 
│ 3 │ 3 │ 
│ 2 │ 4 │ 
└───┴───┘

When SELECT DISTINCT a FROM t1 ORDER BY b ASC is executed to query data, we get the following results:

┌─a─┐ 
│ 2 │ 
│ 1 │ 
│ 3 │ 
└───┘

If we change the sorting direction SELECT DISTINCT a FROM t1 ORDER BY b DESC, we get the following results:

┌─a─┐ 
│ 3 │ 
│ 1 │ 
│ 2 │ 
└───┘

The row 2, 4 is cut before sorting.

LIMIT clause #

LIMIT m allows selecting the start m rows in the results.
LIMIT n, m allows selecting m rows from the rows after skipping the first n results. It is equivalent to the LIMIT m OFFSET n syntax.
n and m must be non-negative integers.
If the ORDER BY clause does not explicitly sort the results, the selection of rows in the result may be arbitrary and non-deterministic.
Syntax

LIMIT m 
LIMIT n, m 
LIMIT m OFFSET n 
LIMIT ... WITH TIES

Example 1: LIMIT m

CREATE TABLE IF NOT EXISTS test.limitClause (id UInt32) engine=CnchMergeTree() order by id; 
INSERT INTO test.limitClause VALUES (1),(2),(3),(4),(5); 
SELECT * FROM test.limitClause LIMIT 2; -- first 2 values will be return

Example 2: LIMIT n, m

CREATE TABLE IF NOT EXISTS test.limitClause (id UInt32) engine=CnchMergeTree() order by id; 
INSERT INTO test.limitClause VALUES (1),(2),(3),(4),(5); 
SELECT * FROM test.limitClause LIMIT 1,2; -- skip first value，next 2 values will be return 
SELECT * FROM test.limitClause LIMIT 2 OFFSET 1; -- skip first value，next 2 values will be return

Example 3: LIMIT ... WITH TIES

SELECT * FROM ( 
    SELECT number%50 AS n FROM numbers(100) 
) ORDER BY n LIMIT 0,5 WITH TIES 

┌─n─┐ 
│ 0 │ 
│ 0 │ 
│ 1 │ 
│ 1 │ 
│ 2 │ 
│ 2 │ 
└───┘

UNION ALL clause #

You can use UNION ALL together with any quantity of SELECT to expand its results.
Syntax

UNION ALL ...

Example

SELECT CounterID, 1 AS table, toInt64(count()) AS c 
    FROM test.hits 
    GROUP BY CounterID 

UNION ALL 

SELECT CounterID, 2 AS table, sum(Sign) AS c 
    FROM test.visits 
    GROUP BY CounterID 
    HAVING c > 0

The result columns are matched by their index (in the internal order of SELECT). If the column names do not match, the final result's name is taken from the first query. Type conversion is performed for the union.
For example, if the two merged queries have the same field with non-Nullable and Nullable types from compatible types, the resulting UNION ALL will have a Nullable type field.
Queries belonging to the following part of UNION ALL cannot be enclosed in parentheses. ORDER BY and LIMIT are applied to individual queries, not to the final result. To apply conversion to the final result, you can place all queries of UNION ALL in a subquery in the FROM clause.

SETTINGS [settingExprList]

[INTO OUTFILE filename]

select * from ttt into outfile '~/abc.txt'

FORMAT format