玩转Mysql系列 - 第11篇:深入了解连接查询及原理
打算提升sql技能的,可以加我微信itsoku,带你成为sql高手。
这是Mysql系列第11篇。
环境:mysql5.7.25,cmd命令中进行演示。
当我们查询的数据来源于多张表的时候,我们需要用到连接查询,连接查询使用率非常高,希望大家都务必掌握。
本文内容
笛卡尔积
内连接
外连接
左连接
右连接
表连接的原理
使用java实现连接查询,加深理解
准备数据
2张表:
t_team:组表。
t_employee:员工表,内部有个team_id引用组表的id。
- drop table if exists t_team;
- create table t_team(
- id int not null AUTO_INCREMENT PRIMARY KEY comment '组id',
- team_name varchar(32) not null default '' comment '名称'
- ) comment '组表';
-
- drop table if exists t_employee;
- create table t_employee(
- id int not null AUTO_INCREMENT PRIMARY KEY comment '部门id',
- emp_name varchar(32) not null default '' comment '员工名称',
- team_id int not null default 0 comment '员工所在组id'
- ) comment '员工表表';
-
- insert into t_team values (1,'架构组'),(2,'测试组'),(3,'java组'),(4,'前端组');
- insert into t_employee values (1,'路人甲Java',1),(2,'张三',2),(3,'李四',3),(4,'王五',0),(5,'赵六',0);
t_team
表4条记录,如下:
- mysql> select * from t_team;
- +----+-----------+
- | id | team_name |
- +----+-----------+
- | 1 | 架构组 |
- | 2 | 测试组 |
- | 3 | java组 |
- | 4 | 前端组 |
- +----+-----------+
- 4 rows in set (0.00 sec)
t_employee
表5条记录,如下:
- mysql> select * from t_employee;
- +----+---------------+---------+
- | id | emp_name | team_id |
- +----+---------------+---------+
- | 1 | 路人甲Java | 1 |
- | 2 | 张三 | 2 |
- | 3 | 李四 | 3 |
- | 4 | 王五 | 0 |
- | 5 | 赵六 | 0 |
- +----+---------------+---------+
- 5 rows in set (0.00 sec)
笛卡尔积
介绍连接查询之前,我们需要先了解一下笛卡尔积。
笛卡尔积简单点理解:有两个集合A和B,笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。
假如A中有m个元素,B中有n个元素,A、B笛卡尔积产生的结果有m*n个结果,相当于循环遍历两个集合中的元素,任意组合。
java伪代码表示如下:
- for(Object eleA : A){
- for(Object eleB : B){
- System.out.print(eleA+","+eleB);
- }
- }
过程:拿A集合中的第1行,去匹配集合B中所有的行,然后再拿集合A中的第2行,去匹配集合B中所有的行,最后结果数量为m*n。
sql中笛卡尔积语法
- select 字段 from 表1,表2[,表N];
- 或者
- select 字段 from 表1 join 表2 [join 表N];
示例:
- mysql> select * from t_team,t_employee;
- +----+-----------+----+---------------+---------+
- | id | team_name | id | emp_name | team_id |
- +----+-----------+----+---------------+---------+
- | 1 | 架构组 | 1 | 路人甲Java | 1 |
- | 2 | 测试组 | 1 | 路人甲Java | 1 |
- | 3 | java组 | 1 | 路人甲Java | 1 |
- | 4 | 前端组 | 1 | 路人甲Java | 1 |
- | 1 | 架构组 | 2 | 张三 | 2 |
- | 2 | 测试组 | 2 | 张三 | 2 |
- | 3 | java组 | 2 | 张三 | 2 |
- | 4 | 前端组 | 2 | 张三 | 2 |
- | 1 | 架构组 | 3 | 李四 | 3 |
- | 2 | 测试组 | 3 | 李四 | 3 |
- | 3 | java组 | 3 | 李四 | 3 |
- | 4 | 前端组 | 3 | 李四 | 3 |
- | 1 | 架构组 | 4 | 王五 | 0 |
- | 2 | 测试组 | 4 | 王五 | 0 |
- | 3 | java组 | 4 | 王五 | 0 |
- | 4 | 前端组 | 4 | 王五 | 0 |
- | 1 | 架构组 | 5 | 赵六 | 0 |
- | 2 | 测试组 | 5 | 赵六 | 0 |
- | 3 | java组 | 5 | 赵六 | 0 |
- | 4 | 前端组 | 5 | 赵六 | 0 |
- +----+-----------+----+---------------+---------+
- 20 rows in set (0.00 sec)
t_team表4条记录,t_employee表5条记录,笛卡尔积结果输出了20行记录。
内连接
语法:
- select 字段 from 表1 inner join 表2 on 连接条件;
- 或
- select 字段 from 表1 join 表2 on 连接条件;
- 或
- select 字段 from 表1, 表2 [where 关联条件];
内连接相当于在笛卡尔积的基础上加上了连接的条件。
当没有连接条件的时候,内连接上升为笛卡尔积。
过程用java伪代码如下:
- for(Object eleA : A){
- for(Object eleB : B){
- if(连接条件是否为true){
- System.out.print(eleA+","+eleB);
- }
- }
- }
示例1:有连接条件
查询员工及所属部门
- mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id;
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- | 张三 | 测试组 |
- | 李四 | java组 |
- +---------------+-----------+
- 3 rows in set (0.00 sec)
-
- mysql> select t1.emp_name,t2.team_name from t_employee t1 join t_team t2 on t1.team_id = t2.id;
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- | 张三 | 测试组 |
- | 李四 | java组 |
- +---------------+-----------+
- 3 rows in set (0.00 sec)
-
- mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id;
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- | 张三 | 测试组 |
- | 李四 | java组 |
- +---------------+-----------+
- 3 rows in set (0.00 sec)
上面相当于获取了2个表的交集,查询出了两个表都有的数据。
示例2:无连接条件
无条件内连接,上升为笛卡尔积,如下:
- mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2;
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- | 路人甲Java | 测试组 |
- | 路人甲Java | java组 |
- | 路人甲Java | 前端组 |
- | 张三 | 架构组 |
- | 张三 | 测试组 |
- | 张三 | java组 |
- | 张三 | 前端组 |
- | 李四 | 架构组 |
- | 李四 | 测试组 |
- | 李四 | java组 |
- | 李四 | 前端组 |
- | 王五 | 架构组 |
- | 王五 | 测试组 |
- | 王五 | java组 |
- | 王五 | 前端组 |
- | 赵六 | 架构组 |
- | 赵六 | 测试组 |
- | 赵六 | java组 |
- | 赵六 | 前端组 |
- +---------------+-----------+
- 20 rows in set (0.00 sec)
示例3:组合条件进行查询
查询架构组的员工,3种写法
- mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id and t2.team_name = '架构组';
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- +---------------+-----------+
- 1 row in set (0.00 sec)
-
- mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id where t2.team_name = '架构组';
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- +---------------+-----------+
- 1 row in set (0.00 sec)
-
- mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id and t2.team_name = '架构组';
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- +---------------+-----------+
- 1 row in set (0.00 sec)
上面3中方式解说。
方式1:on中使用了组合条件。
方式2:在连接的结果之后再进行过滤,相当于先获取连接的结果,然后使用where中的条件再对连接结果进行过滤。
方式3:直接在where后面进行过滤。
总结
内连接建议使用第3种语法,简洁:
select 字段 from 表1, 表2 [where 关联条件];
外连接
外连接涉及到2个表,分为:主表和从表,要查询的信息主要来自于哪个表,谁就是主表。
外连接查询结果为主表中所有记录。如果从表中有和它匹配的,则显示匹配的值,这部分相当于内连接查询出来的结果;如果从表中没有和它匹配的,则显示null。
最终:外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。
外连接分为2种:
左外链接:使用left join关键字,left join左边的是主表。
右外连接:使用right join关键字,right join右边的是主表。
左连接
语法
select 列 from 主表 left join 从表 on 连接条件;
示例1:
查询所有员工信息,并显示员工所在组,如下:
- mysql> SELECT
- t1.emp_name,
- t2.team_name
- FROM
- t_employee t1
- LEFT JOIN
- t_team t2
- ON
- t1.team_id = t2.id;
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- | 张三 | 测试组 |
- | 李四 | java组 |
- | 王五 | NULL |
- | 赵六 | NULL |
- +---------------+-----------+
- 5 rows in set (0.00 sec)
上面查询出了所有员工,员工team_id=0的,team_name为NULL。
示例2:
查询员工姓名、组名,返回组名不为空的记录,如下:
- mysql> SELECT
- t1.emp_name,
- t2.team_name
- FROM
- t_employee t1
- LEFT JOIN
- t_team t2
- ON
- t1.team_id = t2.id
- WHERE
- t2.team_name IS NOT NULL;
- +---------------+-----------+
- | emp_name | team_name |
- +---------------+-----------+
- | 路人甲Java | 架构组 |
- | 张三 | 测试组 |
- | 李四 | java组 |
- +---------------+-----------+
- 3 rows in set (0.00 sec)
上面先使用内连接获取连接结果,然后再使用where对连接结果进行过滤。
右连接
语法
select 列 from 从表 right join 主表 on 连接条件;
示例
我们使用右连接来实现上面左连接实现的功能,如下:
- mysql> SELECT
- t2.team_name,
- t1.emp_name
- FROM
- t_team t2
- RIGHT JOIN
- t_employee t1
- ON
- t1.team_id = t2.id;
- +-----------+---------------+
- | team_name | emp_name |
- +-----------+---------------+
- | 架构组 | 路人甲Java |
- | 测试组 | 张三 |
- | java组 | 李四 |
- | NULL | 王五 |
- | NULL | 赵六 |
- +-----------+---------------+
- 5 rows in set (0.00 sec)
-
- mysql> SELECT
- t2.team_name,
- t1.emp_name
- FROM
- t_team t2
- RIGHT JOIN
- t_employee t1
- ON
- t1.team_id = t2.id
- WHERE
- t2.team_name IS NOT NULL;
- +-----------+---------------+
- | team_name | emp_name |
- +-----------+---------------+
- | 架构组 | 路人甲Java |
- | 测试组 | 张三 |
- | java组 | 李四 |
- +-----------+---------------+
- 3 rows in set (0.00 sec)
理解表连接原理
准备数据
- drop table if exists test1;
- create table test1(
- a int
- );
- drop table if exists test2;
- create table test2(
- b int
- );
- insert into test1 values (1),(2),(3);
- insert into test2 values (3),(4),(5);
- mysql> select * from test1;
- +------+
- | a |
- +------+
- | 1 |
- | 2 |
- | 3 |
- +------+
- 3 rows in set (0.00 sec)
-
- mysql> select * from test2;
- +------+
- | b |
- +------+
- | 3 |
- | 4 |
- | 5 |
- +------+
- 3 rows in set (0.00 sec)
我们来写几个连接,看看效果。
示例1:内连接
- mysql> select * from test1 t1,test2 t2;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 3 |
- | 2 | 3 |
- | 3 | 3 |
- | 1 | 4 |
- | 2 | 4 |
- | 3 | 4 |
- | 1 | 5 |
- | 2 | 5 |
- | 3 | 5 |
- +------+------+
- 9 rows in set (0.00 sec)
-
- mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;
- +------+------+
- | a | b |
- +------+------+
- | 3 | 3 |
- +------+------+
- 1 row in set (0.00 sec)
9条数据正常。
示例2:左连接
- mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;
- +------+------+
- | a | b |
- +------+------+
- | 3 | 3 |
- | 1 | NULL |
- | 2 | NULL |
- +------+------+
- 3 rows in set (0.00 sec)
-
- mysql> select * from test1 t1 left join test2 t2 on t1.a>10;
- +------+------+
- | a | b |
- +------+------+
- | 1 | NULL |
- | 2 | NULL |
- | 3 | NULL |
- +------+------+
- 3 rows in set (0.00 sec)
-
- mysql> select * from test1 t1 left join test2 t2 on 1=1;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 3 |
- | 2 | 3 |
- | 3 | 3 |
- | 1 | 4 |
- | 2 | 4 |
- | 3 | 4 |
- | 1 | 5 |
- | 2 | 5 |
- | 3 | 5 |
- +------+------+
- 9 rows in set (0.00 sec)
上面的左连接第一个好理解。
第2个sql连接条件t1.a>10
,这个条件只关联了test1表,再看看结果,是否可以理解?不理解的继续向下看,我们用java代码来实现连接查询。
第3个sql中的连接条件1=1值为true,返回结果为笛卡尔积。
java代码实现连接查询
下面是一个简略版的实现
- package com.itsoku.sql;
-
- import org.junit.Test;
-
- import java.util.ArrayList;
- import java.util.Arrays;
- import java.util.List;
- import java.util.Objects;
- import java.util.concurrent.CopyOnWriteArrayList;
- import java.util.stream.Collectors;
-
- public class Test1 {
- public static class Table1 {
- int a;
-
- public int getA() {
- return a;
- }
-
- public void setA(int a) {
- this.a = a;
- }
-
- public Table1(int a) {
- this.a = a;
- }
-
- @Override
- public String toString() {
- return "Table1{" +
- "a=" + a +
- '}';
- }
-
- public static Table1 build(int a) {
- return new Table1(a);
- }
- }
-
- public static class Table2 {
- int b;
-
- public int getB() {
- return b;
- }
-
- public void setB(int b) {
- this.b = b;
- }
-
- public Table2(int b) {
- this.b = b;
- }
-
- public static Table2 build(int b) {
- return new Table2(b);
- }
-
- @Override
- public String toString() {
- return "Table2{" +
- "b=" + b +
- '}';
- }
- }
-
- public static class Record<R1, R2> {
- R1 r1;
- R2 r2;
-
- public R1 getR1() {
- return r1;
- }
-
- public void setR1(R1 r1) {
- this.r1 = r1;
- }
-
- public R2 getR2() {
- return r2;
- }
-
- public void setR2(R2 r2) {
- this.r2 = r2;
- }
-
- public Record(R1 r1, R2 r2) {
- this.r1 = r1;
- this.r2 = r2;
- }
-
- @Override
- public String toString() {
- return "Record{" +
- "r1=" + r1 +
- ", r2=" + r2 +
- '}';
- }
-
- public static <R1, R2> Record<R1, R2> build(R1 r1, R2 r2) {
- return new Record(r1, r2);
- }
- }
-
- public static enum JoinType {
- innerJoin, leftJoin
- }
-
-
- public static interface Filter<R1, R2> {
- boolean accept(R1 r1, R2 r2);
- }
-
- public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, Filter<R1, R2> whereFilter) {
- if (Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
- return new ArrayList<>();
- }
-
- List<Record<R1, R2>> result = new CopyOnWriteArrayList<>();
-
- for (R1 r1 : table1) {
- List<Record<R1, R2>> onceJoinResult = joinOn(r1, table2, onFilter);
- result.addAll(onceJoinResult);
- }
-
- if (joinType == JoinType.leftJoin) {
- List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
- List<Record<R1, R2>> leftAppendList = new ArrayList<>();
- for (R1 r1 : table1) {
- if (!r1Record.contains(r1)) {
- leftAppendList.add(Record.build(r1, null));
- }
- }
- result.addAll(leftAppendList);
- }
- if (Objects.nonNull(whereFilter)) {
- for (Record<R1, R2> record : result) {
- if (!whereFilter.accept(record.r1, record.r2)) {
- result.remove(record);
- }
- }
- }
- return result;
- }
-
- public static <R1, R2> List<Record<R1, R2>> joinOn(R1 r1, List<R2> table2, Filter<R1, R2> onFilter) {
- List<Record<R1, R2>> result = new ArrayList<>();
- for (R2 r2 : table2) {
- if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
- result.add(Record.build(r1, r2));
- }
- }
- return result;
- }
-
- @Test
- public void innerJoin() {
- List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
- List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
-
- join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
- System.out.println("-----------------");
- join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
- }
-
- @Test
- public void leftJoin() {
- List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
- List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
-
- join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
- System.out.println("-----------------");
- join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
- }
-
- }
代码中的innerJoin()
方法模拟了下面的sql:
- mysql> select * from test1 t1,test2 t2;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 3 |
- | 2 | 3 |
- | 3 | 3 |
- | 1 | 4 |
- | 2 | 4 |
- | 3 | 4 |
- | 1 | 5 |
- | 2 | 5 |
- | 3 | 5 |
- +------+------+
- 9 rows in set (0.00 sec)
-
- mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;
- +------+------+
- | a | b |
- +------+------+
- | 3 | 3 |
- +------+------+
- 1 row in set (0.00 sec)
运行一下innerJoin()
输出如下:
- Record{r1=Table1{a=1}, r2=Table2{b=3}}
- Record{r1=Table1{a=1}, r2=Table2{b=4}}
- Record{r1=Table1{a=1}, r2=Table2{b=5}}
- Record{r1=Table1{a=2}, r2=Table2{b=3}}
- Record{r1=Table1{a=2}, r2=Table2{b=4}}
- Record{r1=Table1{a=2}, r2=Table2{b=5}}
- Record{r1=Table1{a=3}, r2=Table2{b=3}}
- Record{r1=Table1{a=3}, r2=Table2{b=4}}
- Record{r1=Table1{a=3}, r2=Table2{b=5}}
- -----------------
- Record{r1=Table1{a=3}, r2=Table2{b=3}}
对比一下sql和java的结果,输出的结果条数、数据基本上一致,唯一不同的是顺序上面不一样,顺序为何不一致,稍微介绍。
代码中的leftJoin()
方法模拟了下面的sql:
- mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;
- +------+------+
- | a | b |
- +------+------+
- | 3 | 3 |
- | 1 | NULL |
- | 2 | NULL |
- +------+------+
- 3 rows in set (0.00 sec)
-
- mysql> select * from test1 t1 left join test2 t2 on t1.a>10;
- +------+------+
- | a | b |
- +------+------+
- | 1 | NULL |
- | 2 | NULL |
- | 3 | NULL |
- +------+------+
- 3 rows in set (0.00 sec)
运行leftJoin()
,结果如下:
- Record{r1=Table1{a=3}, r2=Table2{b=3}}
- Record{r1=Table1{a=1}, r2=null}
- Record{r1=Table1{a=2}, r2=null}
- -----------------
- Record{r1=Table1{a=1}, r2=null}
- Record{r1=Table1{a=2}, r2=null}
- Record{r1=Table1{a=3}, r2=null}
效果和sql的效果完全一致,可以对上。
现在我们来讨论java输出的顺序为何和sql不一致?
上面java代码中两个表的连接查询使用了嵌套循环,外循环每执行一次,内循环的表都会全部遍历一次,如果放到mysql中,就相当于内标全部扫描了一次(一次全表io读取操作),主表(外循环)如果有n条数据,那么从表就需要全表扫描n次,表的数据是存储在磁盘中,每次全表扫描都需要做io操作,io操作是最耗时间的,如果mysql按照上面的java方式实现,那效率肯定很低。
那mysql是如何优化的呢?
msql内部使用了一个内存缓存空间,就叫他
join_buffer
吧,先把外循环的数据放到join_buffer
中,然后对从表进行遍历,从表中取一条数据和join_buffer
的数据进行比较,然后从表中再取第2条和join_buffer
数据进行比较,直到从表遍历完成,使用这方方式来减少从表的io扫描次数,当join_buffer
足够大的时候,大到可以存放主表所有数据,那么从表只需要全表扫描一次(即只需要一次全表io读取操作)。mysql中这种方式叫做
Block Nested Loop
。
java代码改进一下,来实现join_buffer的过程。
java代码改进版本
- package com.itsoku.sql;
-
- import org.junit.Test;
-
- import java.util.ArrayList;
- import java.util.Arrays;
- import java.util.List;
- import java.util.Objects;
- import java.util.concurrent.CopyOnWriteArrayList;
- import java.util.stream.Collectors;
-
- import com.itsoku.sql.Test1.*;
-
- public class Test2 {
-
- public static int joinBufferSize = 10000;
- public static List<?> joinBufferList = new ArrayList<>();
-
- public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, Filter<R1, R2> whereFilter) {
- if (Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
- return new ArrayList<>();
- }
-
- List<Test1.Record<R1, R2>> result = new CopyOnWriteArrayList<>();
-
- int table1Size = table1.size();
- int fromIndex = 0, toIndex = joinBufferSize;
- toIndex = Integer.min(table1Size, toIndex);
- while (fromIndex < table1Size && toIndex <= table1Size) {
- joinBufferList = table1.subList(fromIndex, toIndex);
- fromIndex = toIndex;
- toIndex += joinBufferSize;
- toIndex = Integer.min(table1Size, toIndex);
-
- List<Record<R1, R2>> blockNestedLoopResult = blockNestedLoop((List<R1>) joinBufferList, table2, onFilter);
- result.addAll(blockNestedLoopResult);
- }
-
- if (joinType == JoinType.leftJoin) {
- List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
- List<Record<R1, R2>> leftAppendList = new ArrayList<>();
- for (R1 r1 : table1) {
- if (!r1Record.contains(r1)) {
- leftAppendList.add(Record.build(r1, null));
- }
- }
- result.addAll(leftAppendList);
- }
- if (Objects.nonNull(whereFilter)) {
- for (Record<R1, R2> record : result) {
- if (!whereFilter.accept(record.r1, record.r2)) {
- result.remove(record);
- }
- }
- }
- return result;
- }
-
- public static <R1, R2> List<Record<R1, R2>> blockNestedLoop(List<R1> joinBufferList, List<R2> table2, Filter<R1, R2> onFilter) {
- List<Record<R1, R2>> result = new ArrayList<>();
- for (R2 r2 : table2) {
- for (R1 r1 : joinBufferList) {
- if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
- result.add(Record.build(r1, r2));
- }
- }
- }
- return result;
- }
-
- @Test
- public void innerJoin() {
- List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
- List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
-
- join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
- System.out.println("-----------------");
- join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
- }
-
- @Test
- public void leftJoin() {
- List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
- List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
-
- join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
- System.out.println("-----------------");
- join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
- }
- }
执行innerJoin()
,输出:
- Record{r1=Table1{a=1}, r2=Table2{b=3}}
- Record{r1=Table1{a=2}, r2=Table2{b=3}}
- Record{r1=Table1{a=3}, r2=Table2{b=3}}
- Record{r1=Table1{a=1}, r2=Table2{b=4}}
- Record{r1=Table1{a=2}, r2=Table2{b=4}}
- Record{r1=Table1{a=3}, r2=Table2{b=4}}
- Record{r1=Table1{a=1}, r2=Table2{b=5}}
- Record{r1=Table1{a=2}, r2=Table2{b=5}}
- Record{r1=Table1{a=3}, r2=Table2{b=5}}
- -----------------
- Record{r1=Table1{a=3}, r2=Table2{b=3}}
执行leftJoin()
,输出:
- Record{r1=Table1{a=3}, r2=Table2{b=3}}
- Record{r1=Table1{a=1}, r2=null}
- Record{r1=Table1{a=2}, r2=null}
- -----------------
- Record{r1=Table1{a=1}, r2=null}
- Record{r1=Table1{a=2}, r2=null}
- Record{r1=Table1{a=3}, r2=null}
结果和sql的结果完全一致。
扩展
表连接中还可以使用前面学过的group by
、having
、order by
、limit
。
这些关键字相当于在表连接的结果上在进行操作,大家下去可以练习一下,加深理解。
Mysql系列目录
mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!
来源:https://itsoku.blog.csdn.net/article/details/101047548