oracle数据库高级子查询 ①子查询子查询是嵌套在 sql 语句中的另一个select 语句子查询 (内查询) 在主查询执行之前执行主查询(外
oracle数据库高级子查询
①子查询
子查询是嵌套在 sql 语句中的另一个select 语句
子查询 (内查询) 在主查询执行之前执行
主查询(外查询)使用子查询的结果
②多列子查询
主查询与子查询返回的多个列进行比较
where (column1,column2) in (子查询)
例：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
多列子查询中的比较分为两种:
1)成对比较
select employee_id, manager_id, department_id
from employees
where  (manager_id, department_id) in
                      (select manager_id, department_id
                      from  employees
                      where  employee_id in (141,174))
and employee_id not in (141,174);
2)不成对比较
select  employee_id, manager_id, department_id
from    employees
where manager_id in  (select  manager_id
                  from    employees
                  where  employee_id in (174,141))
and  department_id in  (select  department_id
                  from    employees
                  where  employee_id in (174,141))
and employee_id not in(174,141);
③在 from 子句中使用子查询
例：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
1)select last_name,department_id,salary,
(select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary
from employees e1
where salary >
        (select avg(salary)
          from employees e2 
          where e1.department_id = e2.department_id
          group by department_id
          )
2)select  a.last_name, a.salary, a.department_id, b.salavg
from    employees a, (select  department_id,
                      avg(salary) salavg
                      from    employees
                      group by department_id) b
where  a.department_id = b.department_id
and    a.salary > b.salavg;
④单列子查询表达式
单列子查询表达式是在一行中只返回一列的子查询
oracle8i 只在下列情况下可以使用, 例如:
select 语句 (from 和 where 子句)
insert 语句中的values列表中
oracle9i中单列子查询表达式可在下列情况下使用:
decode  和 case
select 中除 group by 子句以外的所有子句中
1)在 case 表达式中使用单列子查询
例：显式员工的employee_id,last_name和location。其中，
若员工department_id与location_id为1800的department_id相同，则location为’canada’,其余则为’usa’。
select employee_id, last_name,
      (case when department_id = (select department_id from departments where location_id = 1800) then 'canada'
      else 'usa' end
 ) location
from  employees;
2)在 order by 子句中使用单列子查询
例：查询员工的employee_id,last_name,要求按照员工的department_name排序
select  employee_id, last_name
from    employees e
order by (select department_name
          from departments d
          where e.department_id = d.department_id);
⑤相关子查询
 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
 select column1, column2, ...
 from  table1 outer
 where  column1 operator (select  colum1, column2
                          from    table2
                          where  expr1 = outer.expr2);
例：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
select last_name, salary, department_id
from  employees outer
where  salary >(select avg(salary)
  from  employees
  where  department_id =  outer.department_id)
例：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，，输出这些相同id的员工的employee_id,last_name和其job_id
select e.employee_id, last_name,e.job_id
from  employees e
where  2             from  job_history
            where  employee_id = e.employee_id);
⑥exists 操作符
exists 操作符检查在子查询中是否存在满足条件的行
1.如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 true
2.如果在子查询中不存在满足条件的行:
条件返回 false
继续在子查询中查找