数据准备
- 学生表(字段:主键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 区别
- on的条件是在连接生成临时表时使用的条件,以左表为基准 ,不管on中的条件真否,都会返回左表中的记录
- 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都会对生成的临时表进行过滤