连接查询
连接查询分为内连接查询和外连接查询
场景一:内连接查询
学生、课程、成绩
表的创建
插入数据
查询示例
要查看 zhang san同学某一门课成绩:预置条件 uid = 1, cid = 2
但是这样并不明显,不知道是哪个同学哪门课的成绩。所以需要多表连接查询,普通方法需要两条语句:
内连接查询:
select a.uid, a.name, a.age, a.sex, c.score from student a
inner join exam c on a.uid=c.uid
再加上筛选条件:
select a.uid, a.name, a.age, a.sex, c.score from student a
inner join exam c on a.uid=c.uid where c.uid = 1 and c.cid = 2;
关于 on a.uid=c.uid 如何区分大表小表?
是按照数据量来区分的,数据量少的是小表,因为小表需要整表搜索,然后去大表搜索。
上面语句就是 先从student小表里取出所有uid,然后拿着这些uid去exam大表里搜索。
没有过滤条件:
如果再想查看课程的具体信息,那么需要把课程表再做一次内连接查询:
其他示例:要查询某一门课超过90分的同学具体信息:只需要改变后面的过滤条件即可
效率问题
若要查看所有学生的成绩信息:使用内连接,然后查看SQL的执行计划,发现先去扫描student表,因为该表数据少,所以当作小表
如果指向查询某一个课程的学生成绩信息:
再看SQL执行计划,发现先去扫描exam表去,把exam表当作小表
如果把 b.cid=3放在连接条件里:发现和上面使用where是一样的,所以:
对于inner join内连接,过滤条件写在where的后面和on连接条件里面,效果是一样的
模板:
SELECT a.属性名1,a.属性名2,…,b,属性名1,b.属性名2… FROM table_name1 a
inner join table_name2 b on a.id = b.id
where a.属性名 满足某些条件;
外连接查询
左连接查询
SELECT a.属性名列表, b.属性名列表 FROM table_name1 a LEFT [OUTER] JOIN table_name2 b on
a.id = b.id;
与内连接的区别:把left这边的表所有的数据显示出来,在右表中不存在相应数据,则显示NULL
select a.* from User a left outer join Orderlist b on a.uid=b.uid where a.orderid is null;
如下图:因为给student表里新增了一个学生,而新增的学生并没有课程和成绩,所以显示NULL
右连接查询
SELECT a.属性名列表, b.属性名列表 FROM table_name1 a RIGHT [OUTER] JOIN table_name2 b on
a.id = b.id;
和内连接区别: 把right这边的表所有的数据显示出来,在左表中不存在相应数据,则显示NULL
select a.* from User a right outer join Orderlist b on a.uid=b.uid
where b.orderid is null;
执行计划对比:
示例1
若要查询哪个同学没有参加过考试,使用in 子查询的语句为:
select * from student where uid not in(select distinct uid from exam);
使用左连接查询:
select a.* from student a left join exam b on a.uid=b.uid where b.cid is null;
示例2
查询哪些同学没有考过某一门课程的考试:
内连接查询
select a.* from student a inner join exam b on a.uid=b.uid
where b.cid=3;
左连接查询
select a.* from student a left join exam b on a.uid=b.uid
where b.cid=3;
发现两种查询结果是一样的,那么来看看执行计划,发现也是一样的,因为左连接一开始并没有扫描a表,而是先执行where,去扫描b表了:
不使用where,使用and 的左连接结果:
select a.* from student a left join exam b on a.uid=b.uid
and b.cid=3;
这个才是左连接查询想要的结果
所以若想使用外连接,过滤条件需要用and写在on连接条件的后面,若有额外条件再往后面加where。