`
qiang106
  • 浏览: 388057 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

子查询转换为连接查询

阅读更多
   我们进行数据查询的时候极少有可能就在一张表里就能得到想要的数据,不可避免得会用到子查询或者连接查询,很多时候我们很轻松自然得会想到子查询的方法,但是子查询往往效率比较低,而转换成连接查询是一种很好的优化方式。

    子查询转换成连接查询又可以分为两种情况,一种是不带聚合的子查询转换,另一种就是带有聚合函数的转换

一、不带聚合函数的子查询转换:

以下是一组测试数据:
use mytest;
drop table  if exists jobs;
CREATE TABLE jobs(
	employee varchar(30),
	title varchar(30)
);
drop table if exists ranks;
CREATE TABLE ranks(
	title varchar(30),
	rank varchar(30)
);
drop table if exists salary;
CREATE TABLE salary(
	rank varchar(30),
	payment int(11)
);

insert into jobs values('张三','经理'),('李四','总经理'),('王五','总经理助理');
insert into ranks values('经理','三'),('总经理','一'),('总经理助理','二');
insert into salary values('一',20000),('二',8000),('三',7000),('四',7000);



   建立了三个表,分别是jobs员工工作表,记录了员工的工作,第二表ranks是岗位等级表,记录每一个工作岗位的等级,第三个表slary自然就是HR为每一个等级的定的薪资标准了。

    现在要知道张三的工资是多少,就需要使用三张表才能得到数据,
使用子查询的方法如下:

select payment from salary 
	where rank=(
		SELECT rank from ranks 
			where title=(
				SELECT title from jobs 
					where employee='张三')
);


转换为连接查询的步骤大致有如下几点:
1、使用表名或者表别名标记所有的列,如显jobs.employee 或者j.employee;
2、将几个子查询的From子名中使用的相同的表用同一个名字或同一别名;
3、将几个Form子句放在一起;
4、将Select及查询的列删除;
5、将第一个之后的Where替换成AND

最后得到如下结果:
select payment from salary s,ranks r,jobs j 
	where j.employee='张三' 
		and j.title = r.title 
		and s.rank = r.rank;


对于需要排除某些条件的查询,如查询岗位等级表中在薪资表中没有工资级别的等级:
select salary.rank 
	from salary 
		where rank 
			not in(select rank from ranks);

使用not in、exists、not exists不失为一种好方法,但同样可以转换成连接查询。如以上的查询可以转换为:
select salary.rank 
	from salary left join ranks 
		on salary.rank=ranks.rank 
			where ranks.rank is null;


二、带聚合函数的子查询向连接查询转换

如下测试数据,有一个订单表,记录了销售人员每天的销售记录,测试数据如下:

DROP TABLE if exists orders;
create table orders(
	customer varchar(30),
	whn date,
	totalitems int(11)
);
insert into orders values('jj','2010-10-10',5),
			('jj','2010-10-11',3),
			('jj','2010-10-12',1),
			('aa','2010-10-10',5),
			('bb','2010-10-10',8),
			('cc','2010-10-10',10);



需要查询每一个销售员最高销售额的日期及销售额时,必然用的聚合函数MAX,以下是最容易想到的查询方式:
select customer,whn,totalitems 
	from orders o1 where o1.totalitems=(
		SELECT max(totalitems) 
			from orders o2 
				where o1.customer = o2.customer
);

此时需要对每一行订单都要进行子查询,因此代码运行速度会很慢,并且老版本的MySQL还不支持子查询,只有一个表,要改成连接查询自然就是自连接了,这里我们需要使用Having子句,
select o1.* from orders o1 join orders o2 
	on(o1.customer=o2.customer) 
		group by o1.customer 
			having o1.totalitems=max(o2.totalitems
);


相信这些我们大学的时候都已经学过,但是没有真正用起来的时候总是那么容易忘记,没有实际操作和体验是感觉不到它的需要,自然也不长记性了,而写下来又是另一种记住的方式。
分享到:
评论

相关推荐

    MySQL慢查询日志.docx

    3. **减少子查询**:尽可能地将子查询转换为连接查询,或者使用临时表来存储中间结果。 4. **使用EXPLAIN分析查询计划**:`EXPLAIN`关键字可以显示MySQL如何执行SQL语句,帮助我们找出潜在的性能问题。 5. **合理...

    子查询课程实验

    - 避免在大表上使用子查询,如果可能,尝试转换为JOIN操作。 - 了解并使用数据库的查询优化器,它可以帮助选择最佳的执行计划。 在进行“子查询课程实验”时,你可以尝试以下练习: 1. 实现单行和多行子查询,...

    mysql explain详解

    4. **避免子查询优化**:尽可能将子查询转换为连接查询,或者使用EXISTS替代IN。 5. **合理使用函数和计算**:避免在`WHERE`子句中使用函数或计算表达式,这可能导致无法使用索引。 6. **避免数据类型不匹配**:确保...

    mysql大数据量优化

    如果可能,应尽量将子查询转换为连接查询。 2. **使用联合查询**: - **UNION**:当需要合并多个SELECT语句的结果集时,联合查询可以替代手动创建临时表。UNION操作会自动去重,但需要注意,它的执行效率通常低于...

    基于SQL数据库的性能优化研究 (1).pdf

    3. 语句优化:语句优化涉及到SQL语句本身的改写,例如通过展开子查询,将子查询转换为连接查询,以提高查询效率。文档没有提供具体的语句优化实例,但一般的语句优化策略可能包括减少不必要的子查询、避免复杂的表...

    Oracle SQL优化

    2. **减少子查询**:子查询可能导致多次数据读取,尝试将子查询转换为连接查询或使用存在谓词,以减少查询复杂度。 3. **使用连接优化**:避免使用嵌套循环连接(Nested Loop Join),特别是在大数据量时。考虑使用...

    数据库优化集锦

    4. **子查询优化**:尽量将子查询转换为连接查询,或者使用exists替代in,以减少查询复杂度。 5. **分页查询**:在大数据量下,使用LIMIT和OFFSET进行分页可能会导致性能下降,可以考虑使用ROW_NUMBER()等函数配合...

    Oracle语句优化53个规则详解

    8. **优化子查询**:尽可能将子查询转换为连接查询,或者使用并集和集合操作。 9. **使用绑定变量**:避免SQL注入,同时提高重用性,减少解析次数。 10. **合理使用临时表**:对于复杂查询,可先将中间结果存入...

    Hibernate 函数 ,子查询 和原生SQL查询

    4. **转换函数**:`cast()`函数可以将一个表达式转换为另一种类型,如将字符串转换为整型。 **子查询** 子查询是嵌套在另一个查询中的查询,它可以作为查询的一部分返回单个值、多个值或整个结果集。在Hibernate中...

    sql-tuning-guide.pdf

    4. 重新编写 SQL 语句可以减少查询的执行时间,例如将子查询转换为连接查询。 索引优化 索引是 Oracle 数据库中的一种重要的性能优化技术。该手册详细介绍了索引的创建、维护和优化方法,包括: 1. 选择合适的...

    oracle性能语句整理

    5. **减少子查询**:尽可能将子查询转换为连接查询,或利用存在的索引。 6. **选择正确的聚合函数**:COUNT(*)比COUNT(1)更快,而COUNT(column)仅计算非NULL值。 四、其他性能优化技巧 1. **表分区**:对于大数据...

    优化sql加快执行速度

    - 尽可能将子查询转换为连接查询,或者使用`EXISTS`或`IN`子句,以利用索引。 10. **使用绑定变量**: - 绑定变量可以防止SQL语句的硬解析,提高执行效率。当SQL语句中的参数是动态变化的,使用绑定变量可以复用...

    oracle tuning

    - 优化子查询:将嵌套的子查询转换为连接查询,或者使用合适的连接类型。 2. **实例参数设置**: - 调整SGA内存:确保SGA配置适合当前的工作负载需求。 - 设置PGA内存:合理分配PGA(程序全局区)可以改善并发...

    SparkSQL子查询源码阅读1

    在解析阶段,SparkSQL会识别并解析子查询,将其转换为可执行的形式。ResolveSubquery可能涉及到将子查询与外部查询关联起来,以便正确地处理关联子查询。 2. **Analyze: CheckAnalysis**: 在这个阶段,SparkSQL...

    面向分布式数据库的相关子查询优化策略.pdf

    子查询展开技术的核心思想是将相关子查询转换为连接操作,以减少对子查询的重复调用和磁盘I/O操作。无用子树切除则是通过分析子查询的结构,移除那些在查询执行过程中不产生任何结果的子树,从而减少不必要的计算和...

    MLDN魔乐科技_Oracle课堂11_子查询

    - **避免在子查询中使用DISTINCT**:这可能导致全表扫描,可以尝试转换为JOIN操作。 - **使用索引**:为子查询中涉及的列创建适当的索引,提高查询速度。 5. **子查询与连接的区别**: - **子查询**:通常更灵活...

    MLDN魔乐科技JAVA培训_Oracle课堂11_子查询.rar

    - **连接优化**:当可能时,尝试将子查询转换为JOIN操作,有时这会带来更好的性能。 - ** EXISTS vs. IN**:在某些情况下,EXISTS子查询比IN操作符更高效,因为它们在找到第一个匹配项时就停止。 5. **案例分析**...

    SQL查询相关技术(常用SQL查询技术、比较逻辑运算符查询、SQL关键字查询、表结构与性能、时间与谓词、聚合函数、子查询与连接查询、交叉表、常用数据操作高级应用、试图存储过程和触发器的使用)

    7. **子查询与连接查询**:子查询是在一个查询语句中嵌套另一个查询,用于提供临时结果供外部查询使用。连接查询(如INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN)则用于合并来自多个表的数据。 8. **交叉表**:...

    MySQL查询优化的5个实用技巧

    将子查询转换为连接查询,尤其是在连接列上有索引时,能显著提高性能。注意,MySQL通常会对第一个表做全表扫描,然后在第二个表上应用过滤条件,因此优化连接列的索引至关重要。 5. **优化UNION操作**: UNION操作...

Global site tag (gtag.js) - Google Analytics