关联子查询都可以改写成两表连接，普通子查询和关联子查询可以放在任何地方：
SELECT last_name, salary, department_idFROM employees outer
WHEREsalary >
(SELECT AVG(salary)
FROM employees
WHEREdepartment_id =
outer.department_id) ;
select e.employee_id, e.last_name , a.avg_sal ,e.salary
from
(SELECT department_id ,AVG(salary)avg_sal
FROM employeesgroupby department_id) a , employees e
where e.department_id=a.department_id and e.salary > a.avg_sal;
select e.employee_id, e.last_name , a.avg_sal ,e.salary
from
(SELECT department_id ,AVG(salary)avg_sal
FROM employeesgroupby department_id) a join employees e
using (department_id )wheree.salary > a.avg_sal;
SELECT employee_id, last_name
FROM employees e
ORDER BY
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
select department_id, department_name,
(select count(*)
from
employees e
where e.department_id = d.department_id) as emp_count
from
departments d;
selectd.department_id, d.department_name , count(*)
from employees e , departments d
where e.department_id(+) =d.department_id
group byd.department_id, d.department_name;
页:
[1]