关联子查询都可以改写成两表连接，普通子查询和关联子查询可以放在任何地方：

botang

1. SELECT last_name, salary, department_id
   
2. FROM   employees outer
   
3. WHERE  salary >
   
4.     (SELECT AVG(salary)
   
5. FROM   employees
   
6. WHERE  department_id =  
   
7.         outer.department_id) ;
   
8.         
   
9.   select    e.employee_id, e.last_name , a.avg_sal   ,e.salary
   
10. from
    
11.   (SELECT     department_id ,  AVG(salary)  avg_sal
    
12.     FROM   employees  group  by department_id) a , employees e
    
13.     where e.department_id=a.department_id   and e.salary > a.avg_sal;
    
14.    
    
15.     select    e.employee_id, e.last_name , a.avg_sal   ,e.salary
    
16. from
    
17.   (SELECT     department_id ,  AVG(salary)  avg_sal
    
18.     FROM   employees  group  by department_id) a    join employees e
    
19.     using (department_id )  where  e.salary > a.avg_sal;
    
20.    

复制代码

1. SELECT   employee_id, last_name
   
2. FROM     employees e
   
3. ORDER BY              
   
4. (SELECT department_name
   
5. FROM departments d
   
6. WHERE e.department_id = d.department_id);

复制代码

1. select department_id, department_name,
   
2. (select count(*)
   
3. from
   
4. employees e
   
5. where e.department_id = d.department_id) as emp_count
   
6. from
   
7. departments d;
   
8. 
   
9. 
   
10. select  d.department_id, d.department_name , count(*)
    
11. from employees e , departments d
    
12. where e.department_id  (+) =d.department_id
    
13. group by  d.department_id, d.department_name;

复制代码