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

外连接

外连接涉及到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)

上⾯查询出了所有员⼯，员⼯teamid=0的，teamname为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，返回结果为笛卡尔积。