Clickhouse: How to more table join?

Created on 28 Feb 2017 · 6Comments · Source: ClickHouse/ClickHouse

:] desc t1

DESCRIBE TABLE t1

┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ x    │ UInt32 │              │                    │
│ y    │ String │              │                    │
└──────┴────────┴──────────────┴────────────────────┘
↗ Progress: 2.00 rows, 86.00 B (1.14 thousand rows/s., 49.14 KB/s.) 
2 rows in set. Elapsed: 0.002 sec. 

:] desc t2

DESCRIBE TABLE t2

┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ x    │ UInt32 │              │                    │
│ y    │ String │              │                    │
└──────┴────────┴──────────────┴────────────────────┘
→ Progress: 2.00 rows, 86.00 B (1.17 thousand rows/s., 50.42 KB/s.) 
2 rows in set. Elapsed: 0.002 sec. 

:] desc t3

DESCRIBE TABLE t3

┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ x    │ UInt32 │              │                    │
│ y    │ String │              │                    │
└──────┴────────┴──────────────┴────────────────────┘
↘ Progress: 2.00 rows, 86.00 B (1.21 thousand rows/s., 52.21 KB/s.) 
2 rows in set. Elapsed: 0.002 sec. 

:]

I can't use this SQL
select x,y from t1 any inner join t2 any inner join t3 using x;
so,how to join more tables?

question

Source

sangli00

Most helpful comment

You have to join tables pairwise. Also, this means you have to manually choose join order:

:) SELECT x, y1, y2, y3 FROM (SELECT x, y AS y3 FROM t3) ALL INNER JOIN (SELECT x, y1, y2 FROM (SELECT x, y AS y1 FROM t1) ALL INNER JOIN (SELECT x, y AS y2 FROM t2) USING (x)) USING (x)

SELECT
    x,
    y1,
    y2,
    y3
FROM
(
    SELECT
        x,
        y AS y3
    FROM t3
)
ALL INNER JOIN
(
    SELECT
        x,
        y1,
        y2
    FROM
    (
        SELECT
            x,
            y AS y1
        FROM t1
    )
    ALL INNER JOIN
    (
        SELECT
            x,
            y AS y2
        FROM t2
    ) USING (x)
) USING (x)

┌─x─┬─y1─┬─y2─┬─y3─┐
│ 1 │ t1 │ t2 │ t3 │
└───┴────┴────┴────┘

1 rows in set. Elapsed: 0.003 sec.

ztlpn on 1 Mar 2017

👍5 😕3 👀1

All 6 comments

You have to join tables pairwise. Also, this means you have to manually choose join order:

:) SELECT x, y1, y2, y3 FROM (SELECT x, y AS y3 FROM t3) ALL INNER JOIN (SELECT x, y1, y2 FROM (SELECT x, y AS y1 FROM t1) ALL INNER JOIN (SELECT x, y AS y2 FROM t2) USING (x)) USING (x)

SELECT
    x,
    y1,
    y2,
    y3
FROM
(
    SELECT
        x,
        y AS y3
    FROM t3
)
ALL INNER JOIN
(
    SELECT
        x,
        y1,
        y2
    FROM
    (
        SELECT
            x,
            y AS y1
        FROM t1
    )
    ALL INNER JOIN
    (
        SELECT
            x,
            y AS y2
        FROM t2
    ) USING (x)
) USING (x)

┌─x─┬─y1─┬─y2─┬─y3─┐
│ 1 │ t1 │ t2 │ t3 │
└───┴────┴────┴────┘

1 rows in set. Elapsed: 0.003 sec.

ztlpn on 1 Mar 2017

👍5 😕3 👀1

so,I will join table A,B,C,D,E.
select ... (select column from C any inner join (select columns from A any inner join B) using (ID) ) using (ID) ......

my god ,If i need A join B and A join C and A join D,
B Join C and B join D...
I can't write this SQL. is very trouble .

sangli00 on 3 Mar 2017

Indeed, joining many tables is currently not very convenient but there are plans to improve the join syntax.

Also note that if many joins are necessary because your schema is some variant of the star schema and you need to join dimension tables to the fact table, then in ClickHouse you should use the external dictionaries feature instead.

ztlpn on 3 Mar 2017

@ztlpn
how to delete table ,this release is not support DELETE ?

sangli00 on 4 Mar 2017

To drop table, run DROP TABLE db.table.
There is no possibility to delete individual records from table.
Except the possibility to delete whole partition, see ALTER TABLE ... DROP PARTITION.