clickhouse支持联表查询的。对常用sql的支持比较好。但是也有出人意料的问题。例如:rigth join
1,写sql时发现a left join b 根 a rigth join b 在clickhouse中不一样。mysql肯定是一样的,为了确认一下,又回到msyql测试了一下,是一样的。
2,准备测试数据
- CREATE TABLE tanktest.tank1 ON CLUSTER clickhouse_test_netjoy
- (
- `id` UInt8 DEFAULT 0 COMMENT 'id',
- `name` Nullable(String) COMMENT '名字',
- `create_time` DateTime DEFAULT toDateTime(now()) COMMENT '创建时间'
- )
- ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/tanktest/tank1', '{replica}')
- PARTITION BY toYYYYMM(create_time)
- PRIMARY KEY id
- ORDER BY (id, create_time)
- CREATE TABLE tanktest.tank1_all ON CLUSTER clickhouse_test_netjoy AS tanktest.tank1
- ENGINE = Distributed(clickhouse_test_netjoy, tanktest, tank1, rand());
- testjian :] select * from tanktest.tank_all;
- SELECT *
- FROM tanktest.tank_all
- ┌─id─┬─name──┬─────────create_time─┐
- │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 1 │ tank1 │ 2021-08-21 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┘
- 4 rows in set. Elapsed: 0.003 sec.
- testjian :] select * from tanktest.tank1_all;
- SELECT *
- FROM tanktest.tank1_all
- ┌─id─┬─name──┬─────────create_time─┐
- │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 2 │ tank1 │ 2021-08-21 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┐
- │ 5 │ tank5 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┘
- 5 rows in set. Elapsed: 0.005 sec.
创建的是Distributed+ReplicatedMergeTree分布表,插入了一些数据。
3,测试一
- testjian :] select * from `tanktest`.`tank_all` as `a` left join `tanktest`.`tank1_all` as `b` on `a`.`id` = `b`.`id`;
- SELECT *
- FROM tanktest.tank_all AS a
- LEFT JOIN tanktest.tank1_all AS b ON a.id = b.id
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 3 │ tank3 │ 2021-08-23 14:21:30 │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 4 │ tank4 │ 2021-08-24 14:21:30 │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 1 │ tank1 │ 2021-08-21 14:21:30 │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │ 2 │ tank1 │ 2021-08-21 14:21:30 │
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- 5 rows in set. Elapsed: 0.006 sec.
- testjian :] select * from `tanktest`.`tank1_all` as `b` right join `tanktest`.`tank_all` as `a` on `b`.`id` = `a`.`id`;
- SELECT *
- FROM tanktest.tank1_all AS b
- RIGHT JOIN tanktest.tank_all AS a ON b.id = a.id
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 3 │ tank3 │ 2021-08-23 14:21:30 │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 2 │ tank1 │ 2021-08-21 14:21:30 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 1 │ tank1 │ 2021-08-21 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 4 │ tank4 │ 2021-08-24 14:21:30 │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 1 │ tank1 │ 2021-08-21 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘
- 9 rows in set. Elapsed: 0.011 sec.
left join根mysql不一样,少了去重功能。
- mysql> select * from `tanktest`.`tank` as `a` left join `tanktest`.`tank1` as `b` on `a`.`id` = `b`.`id`;
- +----+-------+---------------------+------+-------+---------------------+
- | id | name | create_time | id | name | create_time |
- +----+-------+---------------------+------+-------+---------------------+
- | 2 | tank2 | 2021-08-22 14:21:30 | 2 | tank1 | 2021-08-21 14:21:30 |
- | 3 | tank3 | 2021-08-23 14:21:30 | 3 | tank3 | 2021-08-23 14:21:30 |
- | 4 | tank4 | 2021-08-24 14:21:30 | 4 | tank4 | 2021-08-24 14:21:30 |
- | 1 | tank1 | 2021-08-21 14:21:30 | NULL | NULL | NULL |
- +----+-------+---------------------+------+-------+---------------------+
- 4 rows in set (0.00 sec)
4,测试二
- testjian :] select * from `tanktest`.`tank1_all` as `a` left join `tanktest`.`tank_all` as `b` on `a`.`id` = `b`.`id`;
- SELECT *
- FROM tanktest.tank1_all AS a
- LEFT JOIN tanktest.tank_all AS b ON a.id = b.id
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 3 │ tank3 │ 2021-08-23 14:21:30 │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 2 │ tank1 │ 2021-08-21 14:21:30 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 4 │ tank4 │ 2021-08-24 14:21:30 │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
- │ 5 │ tank5 │ 2021-08-24 14:21:30 │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- 5 rows in set. Elapsed: 0.005 sec.
- testjian :] select * from `tanktest`.`tank_all` as `b` right join `tanktest`.`tank1_all` as `a` on `b`.`id` = `a`.`id`;
- SELECT *
- FROM tanktest.tank_all AS b
- RIGHT JOIN tanktest.tank1_all AS a ON b.id = a.id
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 3 │ tank3 │ 2021-08-23 14:21:30 │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 2 │ tank1 │ 2021-08-21 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 5 │ tank5 │ 2021-08-24 14:21:30 │
- └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 4 │ tank4 │ 2021-08-24 14:21:30 │ 4 │ tank4 │ 2021-08-24 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │ 2 │ tank2 │ 2021-08-22 14:21:30 │
- │ 2 │ tank2 │ 2021-08-22 14:21:30 │ 2 │ tank1 │ 2021-08-21 14:21:30 │
- └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
- ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 3 │ tank3 │ 2021-08-23 14:21:30 │
- │ 0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │ 5 │ tank5 │ 2021-08-24 14:21:30 │
- └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘
- 10 rows in set. Elapsed: 0.006 sec.
从测试来看,最好不要用right join,因为返回的结果根预期差距有点大,感觉像交集后的再right join。
转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/clickhouse/2532.html