子查询转化为连接查询的例子

引用

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

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

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

以下是一组测试数据：

Sql代码  
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);  

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为每一个等级的定的薪资标准了。

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

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

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

最后得到如下结果：

Sql代码  
select payment from salary s,ranks r,jobs j    
    where j.employee='张三'    
        and j.title = r.title    
        and s.rank = r.rank;  

select payment from salary s,ranks r,jobs j 
	where j.employee='张三' 
		and j.title = r.title 
		and s.rank = r.rank;

对于需要排除某些条件的查询，如查询岗位等级表中在薪资表中没有工资级别的等级：

Java代码  
select salary.rank    
    from salary    
        where rank    
            not in(select rank from ranks);  

select salary.rank 
	from salary 
		where rank 
			not in(select rank from ranks);

使用not in、exists、not exists不失为一种好方法，但同样可以转换成连接查询。如以上的查询可以转换为：

Sql代码  
select salary.rank    
    from salary left join ranks    
        on salary.rank=ranks.rank    
            where ranks.rank is null;  

select salary.rank 
	from salary left join ranks 
		on salary.rank=ranks.rank 
			where ranks.rank is null;

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

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

Sql代码  
  
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);  


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，以下是最容易想到的查询方式：

Sql代码  
select customer,whn,totalitems    
    from orders o1 where o1.totalitems=(   
        SELECT max(totalitems)    
            from orders o2    
                where o1.customer = o2.customer   
);  

select customer,whn,totalitems 
	from orders o1 where o1.totalitems=(
		SELECT max(totalitems) 
			from orders o2 
				where o1.customer = o2.customer
);

此时需要对每一行订单都要进行子查询，因此代码运行速度会很慢，并且老版本的MySQL还不支持子查询，只有一个表，要改成连接查询自然就是自连接了，这里我们需要使用Having子句，

Sql代码  
select o1.* from orders o1 join orders o2    
    on(o1.customer=o2.customer)    
        group by o1.customer    
            having o1.totalitems=max(o2.totalitems   
);  

select o1.* from orders o1 join orders o2 
	on(o1.customer=o2.customer) 
		group by o1.customer 
			having o1.totalitems=max(o2.totalitems
);

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