clickhouse的left join、any right join、any left join实验

CREATE TABLE jiakai.testJoin1(
id String ,
b String
) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192

CREATE TABLE jiakai.testJoin2(
id String ,
b String
) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192

insert into jiakai.testJoin1 values ('1','b1'),('2','b2'),('3','b3'),('4','b4'),('5','b5')

insert into jiakai.testJoin2 values ('1','b1'),('2','b2'),('3','b3')

select id,t1.b,t2.b as bb from jiakai.testJoin1 t1 left join jiakai.testJoin2 t2 on t1.id=t2.id

id|b |bb|
--|--|--|
1 |b1|b1|
2 |b2|b2|
3 |b3|b3|
4 |b4|  |
5 |b5|  |

select id,t1.b,t2.b as bb from jiakai.testJoin1 t1 left join jiakai.testJoin2 t2 on t1.id=t2.id where bb ='' 

id|b |bb|
--|--|--|
4 |b4|  |
5 |b5|  |

--使用any

select id,t1.b,t2.b as bb from jiakai.testJoin1 t1 any left join jiakai.testJoin2 t2 on t1.id=t2.id where bb =''

id|b |bb|
--|--|--|
4 |b4|  |
5 |b5|  |

--插入一些id重复的值

insert into jiakai.testJoin1 values ('1','b11'),('2','b22')


select id,t1.b,t2.b as bb from jiakai.testJoin1 t1 left join jiakai.testJoin2 t2 on t1.id=t2.id

id|b  |bb|
--|---|--|
1 |b1 |b1|
2 |b2 |b2|
3 |b3 |b3|
4 |b4 |  |
5 |b5 |  |
1 |b11|b1| --也有记录
2 |b22|b2| --也有记录

select id,t1.b,t2.b as bb from jiakai.testJoin1 t1 any left join jiakai.testJoin2 t2 on t1.id=t2.id

id|b  |bb|
--|---|--|
1 |b11|b1| --也有记录
2 |b22|b2| --也有记录
1 |b1 |b1|
2 |b2 |b2|
3 |b3 |b3|
4 |b4 |  |
5 |b5 |  |

--用right join

select id,t1.b,t2.b as bb from jiakai.testJoin1 t1 any right join jiakai.testJoin2 t2 on t1.id=t2.id

id|b  |bb|
--|---|--|
1 |b1 |b1|
2 |b2 |b2|
3 |b3 |b3|
1 |b11|b1| --左表重复的id的记录也查出来了
2 |b22|b2| --左表重复的id的记录也查出来了
--无值的不查出来

--调换方向,小表在左，大表在右

select * from jiakai.testJoin1 t1 order by b

id|b  |
--|---|
1 |b1 |
1 |b11|
2 |b2 |
2 |b22|
3 |b3 |
4 |b4 |
5 |b5 |

 

select t2.id,t2.b,t1.id,t1.b as bb from jiakai.testJoin2 t2 any right join jiakai.testJoin1 t1 on t2.id=t1.id

id|b |t1.id|bb |
--|--|-----|---|
1 |b1|1    |b11|
2 |b2|2    |b22|
3 |b3|3    |b3 |
  |  |4    |b4 |
  |  |5    |b5 |

--右表重复的值没查出来,b1和b2被省略

select t2.id,t2.b,t1.id,t1.b as bb from jiakai.testJoin2 t2 any right join (select * from jiakai.testJoin1 order by b) t1 on t2.id=t1.id

id|b |t1.id|bb|
--|--|-----|--|
1 |b1|1    |b1|
2 |b2|2    |b2|
3 |b3|3    |b3|
  |  |4    |b4|
  |  |5    |b5|

--调整一下右表（大表）的order by顺序

select t2.id,t2.b,t1.id,t1.b as bb from jiakai.testJoin2 t2 any right join (select * from jiakai.testJoin1 order by b desc) t1 on t2.id=t1.id

id|b |t1.id|bb |
--|--|-----|---|
1 |b1|1    |b11|
2 |b2|2    |b22|
3 |b3|3    |b3 |
  |  |4    |b4 |
  |  |5    |b5 |

--==如果右表改为从左表中查出的不重复的id号，左表用回右表

select distinct id from jiakai.testJoin1 t1 order by b

id|
--|
1 |
2 |
3 |
4 |
5 |

--以下语句可以取出表每个id的b值的最大值

select distinct t2.id as id,t1.b from jiakai.testJoin1 t2 any right join (select id,b from jiakai.testJoin1 order by b desc) t1 on t2.id=t1.id

id|t1.b|
--|----|
1 |b11 |
2 |b22 |
3 |b3  |
4 |b4  |
5 |b5  |