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

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

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

一、不带聚合函数的子查询转换： 
以下是一组测试数据： 

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

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