UNION和UNION ALL用法和效率比较、exists和in效率比较_union union all效率

不谈数据库，我们还是好朋友，“宝宝做不到…”

union和union all的用法

union all是将连接的两个查询结果表连接起来；

union是将连接的两个查询结果表连接起来并做去重处理；

UNION用的比较多union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复

1、UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]

2、UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]

效率：
UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。

1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。

2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

exists效率优于in

SQL查询中in和exists的区别分析
select * from A where id in (select id from B);

select * from A where exists (select 1 from B where A.id=B.id);
1
2
3
4

对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。

1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT * FROM (
SELECT s1.SId sid1,s1.CId cid1,s1.score score1,s2.SId sid2,s2.CId cid2,s2.score score2 
FROM sc s1 LEFT JOIN sc s2 ON s1.SId = s2.SId 
WHERE s1.score > s2.score AND s1.CId = '01' AND s2.CId = '02'
)scc 
LEFT JOIN student st ON scc.sid1 = st.SId;
1
2
3
4
5
6

步骤：
先查出查询" 01 “课程比” 02 "课程关联

SELECT s1.SId sid1,s1.CId cid1,s1.score score1,s2.SId sid2,s2.CId cid2,s2.score score2 
FROM sc s1 LEFT JOIN sc s2 ON s1.SId = s2.SId 
WHERE  s1.CId = '01' AND s2.CId = '02'
1
2
3

再比较成绩

SELECT s1.SId sid1,s1.CId cid1,s1.score score1,s2.SId sid2,s2.CId cid2,s2.score score2 
FROM sc s1 LEFT JOIN sc s2 ON s1.SId = s2.SId 
WHERE s1.score > s2.score AND s1.CId = '01' AND s2.CId = '02'
1
2
3

最后与学生表关联

SELECT * FROM (
SELECT s1.SId sid1,s1.CId cid1,s1.score score1,s2.SId sid2,s2.CId cid2,s2.score score2 
FROM sc s1 LEFT JOIN sc s2 ON s1.SId = s2.SId 
WHERE s1.score > s2.score AND s1.CId = '01' AND s2.CId = '02'
)scc 
LEFT JOIN student st ON scc.sid1 = st.SId;
1
2
3
4
5
6

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT st.SId sid2,st.Sname,sc1.score1 FROM (
SELECT s1.SId sid1,AVG(score) score1 FROM sc s1 GROUP BY s1.SId HAVING AVG(score)>=60
)sc1  
LEFT JOIN student st ON sc1.sid1 = st.SId;
1
2
3
4

步骤：
先查成绩表查出平均成绩大于等于60分的Sid和平均成绩，这里需要分组，因为是多组

SELECT s1.SId sid1,AVG(score) score1 
FROM sc s1 
GROUP BY s1.SId 
HAVING AVG(score)>=60;
1
2
3
4

然后于学生表进行关联

SELECT st.SId sid2,st.Sname,sc1.score1 FROM (
SELECT s1.SId sid1,AVG(score) score1 FROM sc s1 GROUP BY s1.SId HAVING AVG(score)>=60
)sc1  
LEFT JOIN student st ON sc1.sid1 = st.SId;

1
2
3
4
5

3.查询在 SC 表存在成绩的学生信息

SELECT DISTINCT st.SId,st.Sname,st.Sage,st.Ssex 
FROM sc s1,student st 
WHERE s1.SId = st.SId;
1
2
3

因为在SC成绩表中有同一同学的不同成绩，所以查出来要去重，用DISTINCT

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

SELECT st.Sname,st.SId stsid,sc1.ccid,sc1.cscore FROM (
SELECT s1.SId sid1,COUNT(CId) ccid,COUNT(score) cscore FROM sc s1 GROUP BY s1.SId
)sc1 RIGHT JOIN student st ON sc1.sid1 = st.SId;
1
2
3

5.查询「李」姓老师的数量

SELECT COUNT(TId) FROM teacher WHERE tname LIKE '李%';
1

6. 查询学过「张三」老师授课的同学的信息

效率较高：

select distinct s.*
from test.student s
inner join test.sc sc on s.SId=sc.SId
inner join test.course c on sc.CId=c.CId
inner join test.teacher t on c.TId=t.TId
where t.Tname='张三'
1
2
3
4
5
6

效率较低：

mysql> select distinct st.* from 
(
    select s.* from sc s,
	(
		SELECT c.CId FROM course c ,
		(SELECT TId FROM teacher  where Tname = "张三") t
		where c.TId = t.TId
	) cid
	where s.CId = cid.CId
)r
join student st on st.SId = r.SId;
1
2
3
4
5
6
7
8
9
10
11

7.查询没有学全所有课程的同学的信息

SELECT * FROM test.student t
where t.SId not in 
(
    select s.SId  from test.sc s 
    group by s.SId 
    having count(s.CId)>=(SELECT COUNT() FROM )
);
1
2
3
4
5
6
7

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT distinct s.SId,t.* FROM sc s , student t
where CId in 
(select CId from sc where sc.SId='01')
and s.SId = t.SId and s.SId !="01";
1
2
3
4