mysql的几种join连接

[TOC]

几种不同的join连接

准备测试数据

use bak;
create table if not exists staff(id varchar(6), name varchar(20), post varchar(8));
create table if not exists hr(id varchar(6), salary int, grade varchar(8));
insert into staff values ('A1','jason','dba');
insert into staff values ('B2','zhang','it');
insert into staff values ('C3','sam','devops');
insert into hr values ('A1',8000,'p5');
insert into hr values ('A1',4000,'m1');   #.id重复
insert into hr values ('B2',6000,'p3');
insert into hr values ('D4',7000,'m2');

内连接 inner join/join

内连接仅返回两个表中完全匹配的行，所以内连接可能会丢失信息
join 其实就是 inner join，是 inner join 缩写

(root@localhost) [bak]> select a.*,b.* from staff a inner join hr b on a.id=b.id;
+------+-------+------+------+--------+-------+
| id   | name  | post | id   | salary | grade |
+------+-------+------+------+--------+-------+
| A1   | jason | dba  | A1   |   8000 | p5    |
| A1   | jason | dba  | A1   |   4000 | m1    |
| B2   | zhang | it   | B2   |   6000 | p3    |
+------+-------+------+------+--------+-------+
3 rows in set (0.00 sec)

(root@localhost) [bak]> select a.*,b.* from staff a join hr b on a.id=b.id;
+------+-------+------+------+--------+-------+
| id   | name  | post | id   | salary | grade |
+------+-------+------+------+--------+-------+
| A1   | jason | dba  | A1   |   8000 | p5    |
| A1   | jason | dba  | A1   |   4000 | m1    |
| B2   | zhang | it   | B2   |   6000 | p3    |
+------+-------+------+------+--------+-------+
3 rows in set (0.00 sec)

inner join 不加筛选条件，返回的是笛卡尔积

(root@localhost) [bak]> select a.*,b.* from staff a inner join hr b;
+------+-------+--------+------+--------+-------+
| id   | name  | post   | id   | salary | grade |
+------+-------+--------+------+--------+-------+
| A1   | jason | dba    | A1   |   8000 | p5    |
| B2   | zhang | it     | A1   |   8000 | p5    |
| C3   | sam   | devops | A1   |   8000 | p5    |
| A1   | jason | dba    | A1   |   4000 | m1    |
| B2   | zhang | it     | A1   |   4000 | m1    |
| C3   | sam   | devops | A1   |   4000 | m1    |
| A1   | jason | dba    | B2   |   6000 | p3    |
| B2   | zhang | it     | B2   |   6000 | p3    |
| C3   | sam   | devops | B2   |   6000 | p3    |
| A1   | jason | dba    | D4   |   7000 | m2    |
| B2   | zhang | it     | D4   |   7000 | m2    |
| C3   | sam   | devops | D4   |   7000 | m2    |
+------+-------+--------+------+--------+-------+
12 rows in set (0.00 sec)

左连接 left join

保留了第一个表满足where条件的所有行，但只包含第二个表与第一个表匹配的行，第二个表相应的空行被放入NULL值
简言之“以左表为主，右表中无对应左表的记录时，右表以null填充”

(root@localhost) [bak]> select a.*,b.* from staff a left join hr b on a.id=b.id;
+------+-------+--------+------+--------+-------+
| id   | name  | post   | id   | salary | grade |
+------+-------+--------+------+--------+-------+
| A1   | jason | dba    | A1   |   8000 | p5    |
| A1   | jason | dba    | A1   |   4000 | m1    |
| B2   | zhang | it     | B2   |   6000 | p3    |
| C3   | sam   | devops | NULL |   NULL | NULL  |
+------+-------+--------+------+--------+-------+
4 rows in set (0.01 sec)

left join 不带on关联条件则会报错（而 inner join 则不会，笛卡尔积嘛）

(root@localhost) [bak]> select a.*,b.* from staff a left join hr b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

(root@localhost) [bak]> select a.*,b.* from staff a left join hr b where a.id=b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.id=b.id' at line 1

右连接 right join

保留了第二个表满足where条件的所有行，但只包含第二个表与第一个表匹配的行，第一个表相应的空行被放入NULL值
简言之“以右表为主，左表中无对应右表的记录时，左表以null填充”

(root@localhost) [bak]> select a.*,b.* from staff a right join hr b on a.id=b.id;
+------+-------+------+------+--------+-------+
| id   | name  | post | id   | salary | grade |
+------+-------+------+------+--------+-------+
| A1   | jason | dba  | A1   |   8000 | p5    |
| A1   | jason | dba  | A1   |   4000 | m1    |
| B2   | zhang | it   | B2   |   6000 | p3    |
| NULL | NULL  | NULL | D4   |   7000 | m2    |
+------+-------+------+------+--------+-------+
4 rows in set (0.01 sec)

right join 不带on关联条件则会报错（而 inner join 则不会，笛卡尔积嘛）

(root@localhost) [bak]> select a.*,b.* from staff a right join hr b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

(root@localhost) [bak]> select a.*,b.* from staff a right join hr b where a.id=b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.id='A1'' at line 1

不支持 full outer join

full outer join 会把满足where条件的两个表所有的行都显示在结果中
MySQL 不支持 full outer join，但可以通过 a left join b UNION a right join b 来实现

(root@localhost) [bak]> select a.*,b.* from staff a full outer join hr b on a.id=b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join hr b on a.id=b.id' at line 1

(root@localhost) [bak]> select a.*,b.* from staff a left join hr b on a.id=b.id union select a.*,b.* from staff a right join hr b on a.id=b.id;
+------+-------+--------+------+--------+-------+
| id   | name  | post   | id   | salary | grade |
+------+-------+--------+------+--------+-------+
| A1   | jason | dba    | A1   |   8000 | p5    |
| A1   | jason | dba    | A1   |   4000 | m1    |
| B2   | zhang | it     | B2   |   6000 | p3    |
| C3   | sam   | devops | NULL |   NULL | NULL  |
| NULL | NULL  | NULL   | D4   |   7000 | m2    |
+------+-------+--------+------+--------+-------+
5 rows in set (0.01 sec)

若要使用 full outer join，也可以考虑使用其他支持该功能的数据库，例如Oracle、SQL Server、PostgreSQL等

注意事项

注意 left join 和 inner join 所返回的不同结果集
注意左连接查询的返回数据记录条数并非是左表的数据总条数

左连接查询，以左表为主，右表中无对应左表的记录时，右表以null填充；当右表中有多条数据对应左表时，左表的记录和对应右表的多条记录便会联合出现多条记录，这种情况下联合查询的总条数便会多于左表的数据记录；当只想返回左表的数据条数时，可通过 group by 左表主键即可。

(root@localhost) [bak]> select a.id from staff a left join hr b on a.id=b.id;
+------+
| id   |
+------+
| A1   |
| A1   |
| B2   |
| C3   |
+------+
4 rows in set (0.00 sec)

(root@localhost) [bak]> select a.id from staff a left join hr b on a.id=b.id group by a.id;
+------+
| id   |
+------+
| A1   |
| B2   |
| C3   |
+------+
3 rows in set (0.00 sec)

注意 where 后面添加过滤条件 b.id != 1 返回的结果集中，除了会过滤联合查询结果中 r.id = 1 的数据，同样会将 r.id is null 的数据过滤掉哦

union 与 union all 的区别

注意 union 与 union all 的区别（UNION去重，而UNION ALL不去重）

(root@localhost) [bak]> select id,name from staff union all select id,name from staff;
+------+-------+
| id   | name  |
+------+-------+
| A1   | jason |
| B2   | zhang |
| C3   | sam   |
| A1   | jason |
| B2   | zhang |
| C3   | sam   |
+------+-------+
6 rows in set (0.00 sec)

(root@localhost) [bak]> select id,name from staff union select id,name from staff;
+------+-------+
| id   | name  |
+------+-------+
| A1   | jason |
| B2   | zhang |
| C3   | sam   |
+------+-------+
3 rows in set (0.00 sec)

筛选条件放在on和where的区别

内连接（inner join）把筛选条件放到where或on里面，效率和结果集一样，因为inner join本身仅返回两个表中完全匹配的行。

(root@localhost) [bak]> select a.*,b.* from staff a inner join hr b on a.id=b.id and name='jason';
+------+-------+------+------+--------+-------+
| id   | name  | post | id   | salary | grade |
+------+-------+------+------+--------+-------+
| A1   | jason | dba  | A1   |   8000 | p5    |
| A1   | jason | dba  | A1   |   4000 | m1    |
+------+-------+------+------+--------+-------+
2 rows in set (0.00 sec)

(root@localhost) [bak]> select a.*,b.* from staff a inner join hr b on a.id=b.id where name='jason';
+------+-------+------+------+--------+-------+
| id   | name  | post | id   | salary | grade |
+------+-------+------+------+--------+-------+
| A1   | jason | dba  | A1   |   8000 | p5    |
| A1   | jason | dba  | A1   |   4000 | m1    |
+------+-------+------+------+--------+-------+
2 rows in set (0.00 sec)

半连接（left/right join）把筛选条件放到where或on里面，效率和结果集则不一样

放在on 中先根据条件查成临时表，再用临时表和基表左关联，基表在关联表中无对应的则以null填充

放在where 中先根据关联条件查出临时表再去除不符合条件的记录，这样基表在右表中无关联的记录会被去掉

(root@localhost) [bak]> select a.*,b.* from staff a left join hr b on a.id=b.id and name='jason';
+------+-------+--------+------+--------+-------+
| id   | name  | post   | id   | salary | grade |
+------+-------+--------+------+--------+-------+
| A1   | jason | dba    | A1   |   8000 | p5    |
| A1   | jason | dba    | A1   |   4000 | m1    |
| B2   | zhang | it     | NULL |   NULL | NULL  |
| C3   | sam   | devops | NULL |   NULL | NULL  |
+------+-------+--------+------+--------+-------+
4 rows in set (0.00 sec)

(root@localhost) [bak]> select a.*,b.* from staff a left join hr b on a.id=b.id where name='jason';
+------+-------+------+------+--------+-------+
| id   | name  | post | id   | salary | grade |
+------+-------+------+------+--------+-------+
| A1   | jason | dba  | A1   |   8000 | p5    |
| A1   | jason | dba  | A1   |   4000 | m1    |
+------+-------+------+------+--------+-------+
2 rows in set (0.00 sec)

mysql的几种join连接

mysql的几种join连接

几种不同的join连接

内连接 inner join/join

左连接 left join

右连接 right join

不支持 full outer join

注意事项

union 与 union all 的区别

筛选条件放在on和where的区别

results matching ""

No results matching ""