自链接
多表链接例题
找出工资大于所在部门平均工资的员工姓名。
多表链接方法 先建个表然后关联查询
create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, avg_sal_dept asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
子查询方法
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
ANY
符合后面子查询的任意一个条件都可以
in
一个取值的列表放在里面
例：select department_name from departments where department_id in (10,20);
例：在Seattle工作的所有员工姓名（使用子查询和多表连接两种方式）
子查询
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
多表链接
select e.last_name from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id and l.city='Seattle';
查找符合下列条件的员工姓名：和Abel在同一个部门，工资比Olson高
select last_name from employees where department_id in(select department_id from employees where last_name='Abel') and salary>(select salary from employees where last_name='Olson');
配对子查询
select last_name, department_id, job_id
from employees
where (department_id, job_id)=
(select department_id, job_id from employees where last_name='Feeney')
and last_name != 'Feeney';