ARRAY JOIN Clause

    Its name comes from the fact that it can be looked at as executing JOIN with an array or nested data structure. The intent is similar to the arrayJoin function, but the clause functionality is broader.

    Syntax:

    You can specify only one ARRAY JOIN clause in a SELECT query.

    Supported types of ARRAY JOIN are listed below:

    • ARRAY JOIN - In base case, empty arrays are not included in the result of JOIN.
    • LEFT ARRAY JOIN - The result of JOIN contains rows with empty arrays. The value for an empty array is set to the default value for the array element type (usually 0, empty string or NULL).

    The examples below demonstrate the usage of the ARRAY JOIN and LEFT ARRAY JOIN clauses. Let’s create a table with an type column and insert values into it:

    1. CREATE TABLE arrays_test
    2. (
    3.     s String,
    4.     arr Array(UInt8)
    5. ) ENGINE = Memory;
    7. INSERT INTO arrays_test
    8. VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
    1. ┌─s───────────┬─arr─────┐
    2. │ Hello       │ [1,2]   │
    3. │ World       │ [3,4,5] │
    4. │ Goodbye     │ []      │
    5. └─────────────┴─────────┘
    1. SELECT s, arr
    2. FROM arrays_test
    3. ARRAY JOIN arr;
    1. ┌─s─────┬─arr─┐
    2. │ Hello │   1 │
    3. │ Hello │   2 │
    4. │ World │   3 │
    5. │ World │   4 │
    6. │ World │   5 │
    7. └───────┴─────┘

    The next example uses the LEFT ARRAY JOIN clause:

    1. SELECT s, arr
    2. FROM arrays_test
    3. LEFT ARRAY JOIN arr;
    1. ┌─s───────────┬─arr─┐
    2. │ Hello       │   1 │
    3. │ Hello       │   2 │
    4. │ World       │   3 │
    5. │ World       │   4 │
    6. │ World       │   5 │
    7. │ Goodbye     │   0 │
    8. └─────────────┴─────┘

    An alias can be specified for an array in the ARRAY JOIN clause. In this case, an array item can be accessed by this alias, but the array itself is accessed by the original name. Example:

    1. SELECT s, arr, a
    2. FROM arrays_test
    3. ARRAY JOIN arr AS a;
    1. ┌─s─────┬─arr─────┬─a─┐
    3. │ World │ [3,4,5] │ 3 │
    4. │ World │ [3,4,5] │ 4 │
    5. │ World │ [3,4,5] │ 5 │
    6. └───────┴─────────┴───┘

    Using aliases, you can perform ARRAY JOIN with an external array. For example:

    1. ┌─s───────────┬─arr_external─┐
    2. │ Hello       │            1 │
    3. │ Hello       │            2 │
    4. │ Hello       │            3 │
    5. │ World       │            1 │
    6. │ World       │            2 │
    7. │ World       │            3 │
    8. │ Goodbye     │            1 │
    9. │ Goodbye     │            2 │
    10. │ Goodbye     │            3 │
    11. └─────────────┴──────────────┘

    Multiple arrays can be comma-separated in the ARRAY JOIN clause. In this case, JOIN is performed with them simultaneously (the direct sum, not the cartesian product). Note that all the arrays must have the same size. Example:

    1. SELECT s, arr, a, num, mapped
    2. FROM arrays_test
    3. ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;
    1. ┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
    2. │ Hello │ [1,2]   │ 1 │   1 │      2 │
    3. │ Hello │ [1,2]   │ 2 │   2 │      3 │
    4. │ World │ [3,4,5] │ 3 │   1 │      4 │
    5. │ World │ [3,4,5] │ 4 │   2 │      5 │
    6. │ World │ [3,4,5] │ 5 │   3 │      6 │
    7. └───────┴─────────┴───┴─────┴────────┘

    The example below uses the arrayEnumerate function:

    1. SELECT s, arr, a, num, arrayEnumerate(arr)
    2. FROM arrays_test
    3. ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
    1. ┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
    2. │ Hello │ [1,2]   │ 1 │   1 │ [1,2]               │
    3. │ Hello │ [1,2]   │ 2 │   2 │ [1,2]               │
    4. │ World │ [3,4,5] │ 3 │   1 │ [1,2,3]             │
    5. │ World │ [3,4,5] │ 4 │   2 │ [1,2,3]             │
    6. │ World │ [3,4,5] │ 5 │   3 │ [1,2,3]             │
    7. └───────┴─────────┴───┴─────┴─────────────────────┘
    1. CREATE TABLE nested_test
    2. (
    3.     s String,
    4.     nest Nested(
    5.     x UInt8,
    6.     y UInt32)
    7. ) ENGINE = Memory;
    9. INSERT INTO nested_test
    10. VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
    2. │ World   │ [3,4,5] │ [30,40,50] │
    3. │ Goodbye │ []      │ []         │
    4. └─────────┴─────────┴────────────┘
    1. SELECT s, `nest.x`, `nest.y`
    2. FROM nested_test
    3. ARRAY JOIN nest;

    When specifying names of nested data structures in ARRAY JOIN, the meaning is the same as ARRAY JOIN with all the array elements that it consists of. Examples are listed below:

    1. SELECT s, `nest.x`, `nest.y`
    2. FROM nested_test
    3. ARRAY JOIN `nest.x`, `nest.y`;
    1. ┌─s─────┬─nest.x─┬─nest.y─┐
    2. │ Hello │      1 │     10 │
    3. │ Hello │      2 │     20 │
    4. │ World │      3 │     30 │
    5. │ World │      4 │     40 │
    6. │ World │      5 │     50 │
    7. └───────┴────────┴────────┘

    This variation also makes sense:

    1. SELECT s, `nest.x`, `nest.y`
    2. FROM nested_test
    3. ARRAY JOIN `nest.x`;
    1. ┌─s─────┬─nest.x─┬─nest.y─────┐
    2. │ Hello │      1 │ [10,20]    │
    3. │ Hello │      2 │ [10,20]    │
    4. │ World │      3 │ [30,40,50] │
    5. │ World │      4 │ [30,40,50] │
    6. │ World │      5 │ [30,40,50] │
    7. └───────┴────────┴────────────┘

    An alias may be used for a nested data structure, in order to select either the JOIN result or the source array. Example:

    1. SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
    2. FROM nested_test
    3. ARRAY JOIN nest AS n;
    1. ┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
    2. │ Hello │   1 │  10 │ [1,2]   │ [10,20]    │
    3. │ Hello │   2 │  20 │ [1,2]   │ [10,20]    │
    4. │ World │   3 │  30 │ [3,4,5] │ [30,40,50] │
    5. │ World │   4 │  40 │ [3,4,5] │ [30,40,50] │
    6. │ World │   5 │  50 │ [3,4,5] │ [30,40,50] │
    7. └───────┴─────┴─────┴─────────┴────────────┘

    Example of using the function:

    1. SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
    2. FROM nested_test
    3. ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
    1. ┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
    2. │ Hello │   1 │  10 │ [1,2]   │ [10,20]    │   1 │
    3. │ Hello │   2 │  20 │ [1,2]   │ [10,20]    │   2 │
    4. │ World │   3 │  30 │ [3,4,5] │ [30,40,50] │   1 │
    5. │ World │   4 │  40 │ [3,4,5] │ [30,40,50] │   2 │
    6. └───────┴─────┴─────┴─────────┴────────────┴─────┘

    The query execution order is optimized when running ARRAY JOIN. Although ARRAY JOIN must always be specified before the WHERE/ clause in a query, technically they can be performed in any order, unless result of is used for filtering. The processing order is controlled by the query optimizer.