Oracle笔记——子查询

Oracle笔记——子查询

• any : 任意一个
• all : 全部
• exists : not exists : 只要exists或not exists后面的结果集不为空，就可以发起查询

实例：查询比20号部门任意员工工资高的员工部门，姓名，岗位 等价于 大于最小的满足任意一个

SELECT a.department_id,
  employee_name,
  employee_job
FROM employees a,
  departments b
WHERE a.department_id = b.department_id
 AND a.department_id != 20
 AND salary > ANY
  (SELECT salary
   FROM employees
   WHERE department_id = 20)


SELECT a.department_id,
  employee_name,
  employee_job
FROM employees a,
  departments b
WHERE a.department_id = b.department_id
 AND a.department_id != 20
 AND salary >
  (SELECT MIN(salary)
   FROM employees
   WHERE department_id = 20)

实例：查询所有比20号部门全部员工工资高的员工部门姓名岗位 ，大于最高等于大于全部

SELECT a.department_id,
  employee_name,
  employee_job
FROM employees a,
  departments b
WHERE a.department_id = b.department_id
 AND a.department_id != 20
 AND salary > ALL
  (SELECT salary
   FROM employees
   WHERE department_id = 20)

SELECT a.department_id,
  employee_name,
  employee_job
FROM employees a,
  departments b
WHERE a.department_id = b.department_id
 AND a.department_id != 20
 AND salary >
  (SELECT MAX(salary)
   FROM employees
   WHERE department_id = 20)

实例：exists : not exists

 select  * from   EMPLOYEES   where  exists (select *  from DEPARTMENTS where DEPARTMENT_ID =10 )

• 在结果集中查询

     select    部门编号 ,  count(员工名称) as 部门的总人员 from 
   (   select  
               DEPARTMENT_ID as 部门编号, 
               EMPLOYEE_NAME as 员工名称, 
               EMPLOYEE_JOB as 工作名称,   
               SALARY as 工资
               from   EMPLOYEES   )  
               group  by   部门编号