从 join 语法到 join 算法
前言
网上各种讲 join 的,讲的我迷糊。各种 cross join
、natural join
、left join
、semi join
等等。
今天就来一次性把他们讲明白了。
构造数据
我们先构造数据,通过例子把它们都讲明白。
create table person(userid int,name char(30)); insert into person values(1001,'张三'); insert into person values(1002,'李四'); insert into person values(1003,'王二麻子'); insert into person values(1004 ,'刘五'); insert into person values(1005, '毛八'); insert into person values(1006, '孙西瓜'); insert into person values(1007, '陈小坏'); insert into person values(1009, '刘妞妞'); create table scorep(userid int,score int); insert into scorep values(1001,100); insert into scorep values(1002,98); insert into scorep values(1004,99); insert into scorep values(1005,89); insert into scorep values(1007,88); insert into scorep values(1008,92);
语法分类
首先,我们看语法层面的 join 类型。就是 可以直接在 sql 层面写出来的。
cross join
cross join 是做笛卡尔积。输出全部数据。
笛卡尔积 select * from person , scorep; select * from person cross join scorep; uid | name | uid | score -------+----------+------+-------- 1001 | 张三 | 1001 | 100 1001 | 张三 | 1002 | 98 1001 | 张三 | 1004 | 99 1001 | 张三 | 1005 | 89 1001 | 张三 | 1007 | 88 1001 | 张三 | 1008 | 92 1002 | 李四 | 1001 | 100 1002 | 李四 | 1002 | 98 1002 | 李四 | 1004 | 99 1002 | 李四 | 1005 | 89 1002 | 李四 | 1007 | 88 1002 | 李四 | 1008 | 92 1003 | 王二麻子 | 1001 | 100 1003 | 王二麻子 | 1002 | 98 1003 | 王二麻子 | 1004 | 99 1003 | 王二麻子 | 1005 | 89 1003 | 王二麻子 | 1007 | 88 1003 | 王二麻子 | 1008 | 92 1004 | 刘五 | 1001 | 100 1004 | 刘五 | 1002 | 98 1004 | 刘五 | 1004 | 99 1004 | 刘五 | 1005 | 89 1004 | 刘五 | 1007 | 88 1004 | 刘五 | 1008 | 92 1005 | 毛八 | 1001 | 100 1005 | 毛八 | 1002 | 98 1005 | 毛八 | 1004 | 99 1005 | 毛八 | 1005 | 89 1005 | 毛八 | 1007 | 88 1005 | 毛八 | 1008 | 92 1006 | 孙西瓜 | 1001 | 100 1006 | 孙西瓜 | 1002 | 98 1006 | 孙西瓜 | 1004 | 99 1006 | 孙西瓜 | 1005 | 89 1006 | 孙西瓜 | 1007 | 88 1006 | 孙西瓜 | 1008 | 92 1007 | 陈小坏 | 1001 | 100 1007 | 陈小坏 | 1002 | 98 1007 | 陈小坏 | 1004 | 99 1007 | 陈小坏 | 1005 | 89 1007 | 陈小坏 | 1007 | 88 1007 | 陈小坏 | 1008 | 92 1009 | 刘妞妞 | 1001 | 100 1009 | 刘妞妞 | 1002 | 98 1009 | 刘妞妞 | 1004 | 99 1009 | 刘妞妞 | 1005 | 89 1009 | 刘妞妞 | 1007 | 88 1009 | 刘妞妞 | 1008 | 92 (48 rows)
Natural join
natural join
是对两张表中字段名和数据类型都相同的字段进行等值连接,并返回符合条件的结果 。 natural join
是自然连接,自动对两个表按照同名的列进行连接
natural join
可以跟 left 、right 、full、inner 。不写默认是 inner join。
语法如下 使用自然连接要注意,两个表同名的列不能超过1个。
select * from person natural join scorep; uid | name | score -------+--------+-------- 1001 | 张三 | 100 1002 | 李四 | 98 1004 | 刘五 | 99 1005 | 毛八 | 89 1007 | 陈小坏 | 88 (5 rows)
inner join
root@localhost:26257/defaultdb> select * from person t1 join scorep t2 on t1.uid = t2.uid; uid | name | uid | score -------+--------+------+-------- 1001 | 张三 | 1001 | 100 1002 | 李四 | 1002 | 98 1004 | 刘五 | 1004 | 99 1005 | 毛八 | 1005 | 89 1007 | 陈小坏 | 1007 | 88 (5 rows)
outer join
Left join
root@localhost:26257/defaultdb> select * from person t1 left join scorep t2 on t1.uid = t2.uid; uid | name | uid | score -------+----------+------+-------- 1001 | 张三 | 1001 | 100 1002 | 李四 | 1002 | 98 1003 | 王二麻子 | NULL | NULL 1004 | 刘五 | 1004 | 99 1005 | 毛八 | 1005 | 89 1006 | 孙西瓜 | NULL | NULL 1007 | 陈小坏 | 1007 | 88 1009 | 刘妞妞 | NULL | NULL (8 rows)
Right join
root@localhost:26257/defaultdb> select * from person t1 right join scorep t2 on t1.uid = t2.uid; uid | name | uid | score -------+--------+------+-------- 1001 | 张三 | 1001 | 100 1002 | 李四 | 1002 | 98 1004 | 刘五 | 1004 | 99 1005 | 毛八 | 1005 | 89 1007 | 陈小坏 | 1007 | 88 NULL | NULL | 1008 | 92 (6 rows)
Full join
root@localhost:26257/defaultdb> select * from person t1 full join scorep t2 on t1.uid = t2.uid; uid | name | uid | score -------+----------+------+-------- 1001 | 张三 | 1001 | 100 1002 | 李四 | 1002 | 98 1003 | 王二麻子 | NULL | NULL 1004 | 刘五 | 1004 | 99 1005 | 毛八 | 1005 | 89 1006 | 孙西瓜 | NULL | NULL 1007 | 陈小坏 | 1007 | 88 1009 | 刘妞妞 | NULL | NULL NULL | NULL | 1008 | 92 (9 rows)
从实现层面来讲讲这几种 join。 首先看看如何解析 join。 然后我们需要一个数据结构来存储这几种join。
这里关键的信息是 jion 条件和 jion类型。
-
条件分为三种 。 on、using 和 nature。
-
类型分为 不写,inner、cross 这三个会转化为 inner 。left、right、full。 四种。
joined_table: | table_ref CROSS opt_join_hint JOIN table_ref { $$.val = &tree.JoinTableExpr{JoinType: tree.AstCross, Left: $1.tblExpr(), Right: $5.tblExpr(), Hint: $3} } | table_ref join_type opt_join_hint JOIN table_ref join_qual { $$.val = &tree.JoinTableExpr{JoinType: $2, Left: $1.tblExpr(), Right: $5.tblExpr(), Cond: $6.joinCond(), Hint: $3} } | table_ref JOIN table_ref join_qual { $$.val = &tree.JoinTableExpr{Left: $1.tblExpr(), Right: $3.tblExpr(), Cond: $4.joinCond()} } | table_ref NATURAL join_type opt_join_hint JOIN table_ref { $$.val = &tree.JoinTableExpr{JoinType: $3, Left: $1.tblExpr(), Right: $6.tblExpr(), Cond: tree.NaturalJoinCond{}, Hint: $4} } | table_ref NATURAL JOIN table_ref { $$.val = &tree.JoinTableExpr{Left: $1.tblExpr(), Right: $4.tblExpr(), Cond: tree.NaturalJoinCond{}} }
type JoinTableExpr struct { JoinType string Left TableExpr Right TableExpr Cond JoinCond Hint string } // JoinTableExpr.Join const ( AstFull = "FULL" AstLeft = "LEFT" AstRight = "RIGHT" AstCross = "CROSS" AstInner = "INNER" ) // JoinTableExpr.Hint const ( AstHash = "HASH" AstLookup = "LOOKUP" AstMerge = "MERGE" AstInverted = "INVERTED" )
Left anti join
select * from person t1 left anti join scorep t2 on t1.uid = t2.uid -- not in root@localhost:26257/defaultdb> select * from person where uid not in (select uid from scorep); uid | name -------+----------- 1003 | 王二麻子 1006 | 孙西瓜 1009 | 刘妞妞 (3 rows) -- not exists root@localhost:26257/defaultdb> select * from person t1 where not exists (select * from scorep t2 where t2.uid = t1.uid); uid | name -------+----------- 1003 | 王二麻子 1006 | 孙西瓜 1009 | 刘妞妞 (3 rows)
Left semi join
select * from person t1 left semi join scorep t2 on t1.uid = t2.uid; root@localhost:26257/defaultdb> select * from person where uid in (select uid from scorep); uid | name -------+--------- 1001 | 张三 1002 | 李四 1004 | 刘五 1005 | 毛八 1007 | 陈小坏 (5 rows) root@localhost:26257/defaultdb> select * from person t1 where exists (select * from scorep t2 where t2.uid = t1.uid); uid | name -------+--------- 1001 | 张三 1002 | 李四 1004 | 刘五 1005 | 毛八 1007 | 陈小坏 (5 rows)
join 算法分类
hash join, sort merge jion。nestloop join