子查询

子查询

子查询效率

子查询会创建临时表，查询结束后需要对临时表进行销毁，如果临时表过大，消耗会非常大。查询大量数据时，不建议使用子查询，使用连接查询代替，也可以考虑在应用中进行处理。

--写一个查询显示与 Zlotkey 在同一部门的雇员的 lastname 和 hiredate，结果中不包括 Zlotkey。
--子查询
select last_name, hire_date 
from employees emp
where department_id = 
(select department_id from employees where last_name = 'Zlotkey')
and last_name <> 'Zlotkey';
--连接查询
select emp.last_name, emp.hire_date
from employees emp, employees z
where emp.employee_id <> z.employee_id 
and z.last_name = 'Zlotkey' 
and emp.department_id = z.department_id;

--创建一个查询显示所有薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。
--子查询
select employee_id, last_name, salary
from employees 
where salary > (select avg(salary) from employees)
order by salary;

/*写一个查询显示
所有工作在有任一雇员的名字中包含一个u
的部门的雇员的雇员号和名字。
*/
--子查询
select employee_id, last_name 
from employees
where department_id in(
  select distinct department_id
  from employees
  where instr(last_name, 'u') > 0
)

--连接查询
select distinct e1.employee_id, e1.last_name 
from employees e1, employees e2
where e1.department_id = e2.department_id
and instr(e2.last_name, 'u') > 0

/*
显示所有部门地点号 (department location ID ) 是 1700 的雇员的
 lastname、departmentnumber 和 jobID。
*/
--子查询
select last_name, department_id, job_ID
from employees
where department_id in
(
  select department_id
  from departments
  where location_ID = 1700
)

--连接查询
select emp.last_name, emp.department_id, emp.job_ID
from employees emp, departments dep
where emp.department_id = dep.department_id
and dep.location_id = 1700

/*
显示每个向 King 报告的雇员的名字和薪水。
*/
--子查询
select last_name, salary
from employees
where manager_id in (
  select employee_id 
  from employees
  where last_name = 'King'
);
--连接查询
select distinct e1.last_name, e1.salary
from employees e1, employees e2
where e1.manager_id = e2.employee_id
and e2.last_name = 'King';

/*
显 示 在 Executive 部 门 的 每 个 雇 员 的 department number、lastname 和 jobID。
*/
--子查询
select department_id, last_name
from employees
where department_id = (
  select department_id
  from departments
  where department_name = 'Executive'
);

--连接查询
select distinct emp.department_id, emp.last_name
from employees emp, departments dep
where emp.department_id = dep.department_id
and dep.department_name = 'Executive'

/*
查询显示所有收入高于平均薪水并且工作在有任一雇员的名字中
带有一个 u 的部门的雇员的 employeenumbers、 last names 和 salaries。
*/
select distinct e1.employee_id, e1.last_name 
from employees e1, employees e2
where e1.department_id = e2.department_id
and instr(e2.last_name, 'u') > 0
and e1.salary > (select avg(salary) from employees)