MySQL数据库，深入了解连接查询及原理(一)

当我们查询的数据来源于多张表的时候，我们需要⽤到连接查询，连接查询使⽤率⾮常⾼，是学习数据库必须掌握的。

内容提要：

1. 笛卡尔积

2. 内连接

这是Mysql系列第11篇。

环境：mysql5.7.25，cmd命令中进⾏演⽰。

3. 外连接

4. 左连接

5. 右连接

6. 表连接的原理

7. 使⽤java实现连接查询，加深理解

准备数据

2张表：

t_team：组表。

temployee：员⼯表，内部有个teamid引⽤组表的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)

tteam表4条记录，temployee表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 关联条件];