外连接
外连接涉及到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,返回结果为笛卡尔积。