mysql 连接查询

数据准备

• 学生表（字段：主键ID、学生编号、科目编号、学生姓名）

create table if not exists `students_test` (
  `id` int(11) not null auto_increment,
  `stuid` int(11) not null,
  `subid` int(11) default null,
  `stuname` varchar(50) not null,
  primary key (`id`)
) engine=innodb default charset=utf8;

insert into `students`(`stuid`, `subid`, `stuname`) values(1001, 101, "张三");
insert into `students`(`stuid`, `subid`, `stuname`) values(1002, 102, "李四");
insert into `students`(`stuid`, `stuname`) values(1003, "王五");
insert into `students`(`stuid`, `subid`, `stuname`) values(1004, 103, "李丽");
insert into `students`(`stuid`, `subid`, `stuname`) values(1005, 104, "刘能");

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

• 科目表（字段：主键ID、科目编号、科目名称）

create table if not exists `subjects` (
  `id` int(11) not null auto_increment,
  `subid` int(11) not null,
  `subname` varchar(50) not null,
  primary key (`id`)
) engine=innodb default charset=utf8;

insert into `subjects`(`subid`, `subname`) values(101, "语文");
insert into `subjects`(`subid`, `subname`) values(102, "英语");
insert into `subjects`(`subid`, `subname`) values(103, "数学");
insert into `subjects`(`subid`, `subname`) values(104, "体育");
insert into `subjects`(`subid`, `subname`) values(105, "音乐");

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

1.inner join

说明：内连接（等值连接）获取两张表相同的记录

实例：查询参加了科目的学生及科目信息（学生ID、学生姓名、科目名称）

mysql> select stu.stuid, stu.stuname, sub.subname from students stu inner join subjects sub on stu.subid = sub.subid;
+-------+---------+---------+
| stuid | stuname | subname |
+-------+---------+---------+
|  1001 | 张三    | 语文    |
|  1002 | 李四    | 英语    |
|  1004 | 李丽    | 数学    |
|  1005 | 刘能    | 体育    |
+-------+---------+---------+
4 rows in set (0.00 sec)

2.left join

说明：左外连接，即 left join，会读取左表全部数据，右表有则读取，无则使用NULL填充

实例1：查询学生信息及科目信息（学生ID、学生姓名、科目ID，科目名称）

mysql> select stu.stuid, stu.stuname, sub.subid, sub.subname from students stu left join subjects sub on stu.subid = sub.subid;
+-------+---------+-------+---------+
| stuid | stuname | subid | subname |
+-------+---------+-------+---------+
|  1001 | 张三    |   101 | 语文    |
|  1002 | 李四    |   102 | 英语    |
|  1004 | 李丽    |   103 | 数学    |
|  1005 | 刘能    |   104 | 体育    |
|  1003 | 王五    |  NULL | NULL    |
+-------+---------+-------+---------+
5 rows in set (0.00 sec)

3. right join

说明：与左外连接相反，right join，会读取右表全部数据，左表有则读取，无则使用NULL填充

实例：查询科目信息及学生信息（科目ID、科目名称、学生ID、学生姓名）

mysql> select sub.subid, sub.subname, stu.stuid, stu.stuname from students stu right join subjects sub on stu.subid = sub.subid;
+-------+---------+-------+---------+
| subid | subname | stuid | stuname |
+-------+---------+-------+---------+
|   101 | 语文    |  1001 | 张三    |
|   102 | 英语    |  1002 | 李四    |
|   103 | 数学    |  1004 | 李丽    |
|   104 | 体育    |  1005 | 刘能    |
|   105 | 音乐    |  NULL | NULL    |
+-------+---------+-------+---------+
5 rows in set (0.00 sec)

4.union 与 union all

oracle 里面有 full join，但是在 mysql 中没有 full join。我们可以使用 union 来达到目的。
union 与 union all 都是将两表关联，查询它们的所有记录，union会去重，union all 不去重，会有重复记录
注意：

• union 会去重，所以会进行重复扫描，所以效率低，若没有要刻意删除重复行，则使用union all
• 两个要联合的sql字段个数必须一样，且字段类型要“相容”（一致）
• 使用union合并结果集时，若需要进行使用order by排序，不需要在每个sql中都使用order by，最后一个sql中使用即可。

总结

连接方式	说明
inner join	内连接，获取两表中相同的数据
left join	左连接，获取左表全部数据，右表有则读取，无则使用 NULL 填充
right join	右连接，获取右表全部数据，左表有则读取，无则使用 NULL 填充
full join	mysql不支持，可以通过 union 来实现

on and 与 on where 区别

1. on的条件是在连接生成临时表时使用的条件,以左表为基准 ,不管on中的条件真否,都会返回左表中的记录
2. where条件是在临时表生成好后,再对临时表过滤。此时 和left join有区别(返回左表全部记录),条件不为真就全部过滤掉,on后的条件来生成左右表关联的临时表，where后的条件是生成临时表后对临时表过滤

on and是进行韦恩运算时 连接时就做的动作,where是全部连接完后，再根据条件过滤

1).left join

• 实例1：left join on

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from students stu left join subjects sub on stu.subid = sub.subid;
+----+-------+-------+---------+------+-------+---------+
| id | stuid | subid | stuname | id   | subid | subname |
+----+-------+-------+---------+------+-------+---------+
|  1 |  1001 |   101 | 张三    |    1 |   101 | 语文    |
|  2 |  1002 |   102 | 李四    |    2 |   102 | 英语    |
|  4 |  1004 |   103 | 李丽    |    3 |   103 | 数学    |
|  5 |  1005 |   104 | 刘能    |    4 |   104 | 体育    |
|  3 |  1003 |  NULL | 王五    | NULL |  NULL | NULL    |
+----+-------+-------+---------+------+-------+---------+
5 rows in set (0.00 sec)

• 实例2：left join on and

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from students stu left join subjects sub on stu.subid = sub.subid and stu.subid = 101;
+----+-------+-------+---------+------+-------+---------+
| id | stuid | subid | stuname | id   | subid | subname |
+----+-------+-------+---------+------+-------+---------+
|  1 |  1001 |   101 | 张三    |    1 |   101 | 语文    |
|  2 |  1002 |   102 | 李四    | NULL |  NULL | NULL    |
|  3 |  1003 |  NULL | 王五    | NULL |  NULL | NULL    |
|  4 |  1004 |   103 | 李丽    | NULL |  NULL | NULL    |
|  5 |  1005 |   104 | 刘能    | NULL |  NULL | NULL    |
+----+-------+-------+---------+------+-------+---------+
5 rows in set (0.01 sec)

• 实例3：left join on where

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from students stu left join subjects sub on stu.subid = sub.subid where stu.subid = 101;
+----+-------+-------+---------+------+-------+---------+
| id | stuid | subid | stuname | id   | subid | subname |
+----+-------+-------+---------+------+-------+---------+
|  1 |  1001 |   101 | 张三    |    1 |   101 | 语文    |
+----+-------+-------+---------+------+-------+---------+
1 row in set (0.00 sec)

• 实例4：左表独有（查询左表独有的数据）

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from students stu left join subjects sub on stu.subid = sub.subid where sub.subid is null;
+----+-------+-------+---------+------+-------+---------+
| id | stuid | subid | stuname | id   | subid | subname |
+----+-------+-------+---------+------+-------+---------+
|  3 |  1003 |  NULL | 王五    | NULL |  NULL | NULL    |
+----+-------+-------+---------+------+-------+---------+
1 row in set (0.00 sec)

总结：查询左表独有数据，使用左外连接a left join b on，where 条件中使用 b.field is null 即可

• 实例5：右表独有（查询右边独有的数据）

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from students stu right join subjects sub on sub.subid = stu.subid where stu.stuid is null;
+------+-------+-------+---------+----+-------+---------+
| id   | stuid | subid | stuname | id | subid | subname |
+------+-------+-------+---------+----+-------+---------+
| NULL |  NULL |  NULL | NULL    |  5 |   105 | 音乐    |
+------+-------+-------+---------+----+-------+---------+
1 row in set (0.00 sec)

总结：查询右表独有数据，使用右外连接a right join b on，where 条件中使用 a.field is null 即可

3).inner join

实例：on and 和 on where 结果一致

mysql> select * from students;
+----+-------+-------+---------+
| id | stuid | subid | stuname |
+----+-------+-------+---------+
|  1 |  1001 |   101 | 张三    |
|  2 |  1002 |   102 | 李四    |
|  3 |  1003 |  NULL | 王五    |
|  4 |  1004 |   103 | 李丽    |
|  5 |  1005 |   104 | 刘能    |
+----+-------+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from subjects;
+----+-------+---------+
| id | subid | subname |
+----+-------+---------+
|  1 |   101 | 语文    |
|  2 |   102 | 英语    |
|  3 |   103 | 数学    |
|  4 |   104 | 体育    |
|  5 |   105 | 音乐    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> select * from students stu inner join subjects sub on stu.subid = sub.subid and stu.subid = 101;
+----+-------+-------+---------+----+-------+---------+
| id | stuid | subid | stuname | id | subid | subname |
+----+-------+-------+---------+----+-------+---------+
|  1 |  1001 |   101 | 张三    |  1 |   101 | 语文    |
+----+-------+-------+---------+----+-------+---------+
1 row in set (0.00 sec)

mysql> select * from students stu inner join subjects sub on stu.subid = sub.subid and sub.subid = 101;
+----+-------+-------+---------+----+-------+---------+
| id | stuid | subid | stuname | id | subid | subname |
+----+-------+-------+---------+----+-------+---------+
|  1 |  1001 |   101 | 张三    |  1 |   101 | 语文    |
+----+-------+-------+---------+----+-------+---------+
1 row in set (0.00 sec)

mysql> select * from students stu inner join subjects sub on stu.subid = sub.subid where stu.subid = 101;
+----+-------+-------+---------+----+-------+---------+
| id | stuid | subid | stuname | id | subid | subname |
+----+-------+-------+---------+----+-------+---------+
|  1 |  1001 |   101 | 张三    |  1 |   101 | 语文    |
+----+-------+-------+---------+----+-------+---------+
1 row in set (0.00 sec)

mysql> select * from students stu inner join subjects sub on stu.subid = sub.subid where sub.subid = 101;
+----+-------+-------+---------+----+-------+---------+
| id | stuid | subid | stuname | id | subid | subname |
+----+-------+-------+---------+----+-------+---------+
|  1 |  1001 |   101 | 张三    |  1 |   101 | 语文    |
+----+-------+-------+---------+----+-------+---------+
1 row in set (0.00 sec)

在使用inner join时，不管是对左表还是右表进行筛选，on and和on where都会对生成的临时表进行过滤