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

本文深入讲解SQL中的union与union all的使用区别,分析exists与in的效率对比,并提供多个复杂查询案例,如成绩对比、平均成绩计算等,帮助读者掌握高级SQL查询技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

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);

对于以上两种情况,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;

步骤:
先查出查询" 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'

再比较成绩

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'

最后与学生表关联

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;

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;

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

SELECT s1.SId sid1,AVG(score) score1 
FROM sc s1 
GROUP BY s1.SId 
HAVING AVG(score)>=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;

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

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

因为在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;

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

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

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='张三'

效率较低:

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;

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 )
);

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";
SQL查询中使用`UNION ALL`结合`IN`查询条件时,可以通过以下几种方式进行优化: 1. **使用临时表**: 将`IN`查询的结果先存储在一个临时表中,然后与其他表进行`JOIN`操作,而不是使用`IN`子查询。这种方式可以减少子查询的嵌套层数,提高查询效率。 ```sql CREATE TEMPORARY TABLE temp_table AS SELECT column1 FROM table1 WHERE condition; SELECT * FROM ( SELECT column1, column2 FROM table2 UNION ALL SELECT column1, column2 FROM table3 ) AS subquery JOIN temp_table ON subquery.column1 = temp_table.column1; ``` 2. **使用`EXISTS`替代`IN`**: 在某些情况下,`EXISTS`的性能优于`IN`,特别是在处理大数据集时。`EXISTS`通常会更快,因为它会在找到第一个匹配项后立即停止搜索。 ```sql SELECT * FROM ( SELECT column1, column2 FROM table2 WHERE EXISTS ( SELECT 1 FROM table1 WHERE table2.column1 = table1.column1 AND condition ) UNION ALL SELECT column1, column2 FROM table3 WHERE EXISTS ( SELECT 1 FROM table1 WHERE table3.column1 = table1.column1 AND condition ) ) AS subquery; ``` 3. **使用`JOIN`替代`IN`**: 如果`IN`子查询可以转换为`JOIN`操作,通常`JOIN`的性能会更好。 ```sql SELECT DISTINCT subquery.* FROM ( SELECT column1, column2 FROM table2 UNION ALL SELECT column1, column2 FROM table3 ) AS subquery JOIN table1 ON subquery.column1 = table1.column1 WHERE condition; ``` 4. **索引优化**: 确保在`IN`子查询中使用的列上有适当的索引。索引可以显著提高查询性能,特别是在大数据集上。 5. **避免不必要的列**: 只选择需要的列,避免使用`SELECT *`。这可以减少数据传输处理的开销。 通过这些优化方法,可以显著提高`UNION ALL`结合`IN`查询条件的查询性能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

偷偷学习被我发现

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值