Tuple(T1, T2, ...)

A tuple of elements, each having an individual type. Tuple must contain at least one element.

Tuples are used for temporary column grouping. Columns can be grouped when an IN expression is used in a query, and for specifying certain formal parameters of lambda functions. For more information, see the sections IN operators and Higher order functions.

Tuples can be the result of a query. In this case, for text formats other than JSON, values are comma-separated in (). In JSON formats, tuples are output as arrays (in []).

Creating Tuples

You can use a function to create a tuple:

tuple(T1, T2, ...)

Example of creating a tuple:

SELECT tuple(1, 'a') AS x, toTypeName(x)

┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │
└─────────┴───────────────────────────┘

A Tuple can contain a single element

Example:

SELECT tuple('a') AS x;

┌─x─────┐
│ ('a') │
└───────┘

Syntax (tuple_element1, tuple_element2) may be used to create a tuple of several elements without calling the tuple() function.

Example:

SELECT (1, 'a') AS x, (today(), rand(), 'someString') AS y, ('a') AS not_a_tuple;

┌─x───────┬─y──────────────────────────────────────┬─not_a_tuple─┐
│ (1,'a') │ ('2022-09-21',2006973416,'someString') │ a           │
└─────────┴────────────────────────────────────────┴─────────────┘

Data Type Detection

When creating tuples on the fly, ClickHouse interferes the type of the tuples arguments as the smallest types which can hold the provided argument value. If the value is NULL, the interfered type is Nullable.

Example of automatic data type detection:

SELECT tuple(1, NULL) AS x, toTypeName(x)

┌─x─────────┬─toTypeName(tuple(1, NULL))──────┐
│ (1, NULL) │ Tuple(UInt8, Nullable(Nothing)) │
└───────────┴─────────────────────────────────┘

Referring to Tuple Elements

Tuple elements can be referred to by name or by index:

CREATE TABLE named_tuples (`a` Tuple(s String, i Int64)) ENGINE = Memory;
INSERT INTO named_tuples VALUES (('y', 10)), (('x',-10));

SELECT a.s FROM named_tuples; -- by name
SELECT a.2 FROM named_tuples; -- by index

┌─a.s─┐
│ y   │
│ x   │
└─────┘

┌─tupleElement(a, 2)─┐
│                 10 │
│                -10 │
└────────────────────┘

Comparison operations with Tuple

Two tuples are compared by sequentially comparing their elements from the left to the right. If first tuples element is greater (smaller) than the second tuples corresponding element, then the first tuple is greater (smaller) than the second, otherwise (both elements are equal), the next element is compared.

Example:

SELECT (1, 'z') > (1, 'a') c1, (2022, 01, 02) > (2023, 04, 02) c2, (1,2,3) = (3,2,1) c3;

┌─c1─┬─c2─┬─c3─┐
│  1 │  0 │  0 │
└────┴────┴────┘

Real world examples:

CREATE TABLE test
(
    `year` Int16,
    `month` Int8,
    `day` Int8
)
ENGINE = Memory AS
SELECT *
FROM values((2022, 12, 31), (2000, 1, 1));

SELECT * FROM test;

┌

Nullable(Tuple(T1, T2, …))

Allows the entire tuple to be NULL, as opposed to Tuple(Nullable(T1), Nullable(T2), ...) where only individual elements can be NULL.

Example:

SET enable_nullable_tuple_type = 1;

CREATE TABLE test (
    id UInt32,
    data Nullable(Tuple(String, Int64))
) ENGINE = Memory;

INSERT INTO test VALUES (1, ('hello', 42)), (2, NULL);

SELECT * FROM test WHERE data IS NULL;

┌─id─┬─data─┐
│  2 │ ᴺᵁᴸᴸ │
└────┴──────┘