25.高级子查询

1.多列子查询

--主查询中每条记录都会与多条记录和多字段子查询得结果进行比较
--列对比匹配原则
----多字段子查询得字段比较有两种
------成对比较
hr@ORCLPDB01 2023-02-26 19:31:40> select employee_id,manager_id,deparmetn_id
  2  from emp1_demo
  3  where (manager_id,department_id) in (select manager_id, department_id from emp1_demo where first_name = 'John')
  4  and first_name <> 'John';
------非成对比较
hr@ORCLPDB01 2023-02-26 19:31:40> select employee_id,manager_id,deparmetn_id
  2  from emp1_demo
  3  where manager_id in (select manager_id from emp1_demo where first_name = 'John')
  4  and department_id in (select department_id from emp1_demo where first_name = 'John')
  5  and first_name <> 'John';

2.标准子查询

hr@ORCLPDB01 2023-02-26 19:33:31> select employee_id,last_name,
  2  (case when department_id = 
  3                            ( select department_id from departments where location_id = 1800) then 'Canada'
  4        else 'USA' end ) location
  5  from employees;

EMPLOYEE_ID LAST_NAME		      LOCATI
----------- ------------------------- ------
	199 Grant		      USA
	200 Whalen		      USA
	201 Hartstein		      Canada
	202 Fay 		      Canada
	203 Mavris		      USA
    
hr@ORCLPDB01 2023-02-26 19:42:18> r
  1  select employee_id, last_name
  2  from employees e
  3  order by ( select department_name 
  4             from departments d
  5*		where e.department_id = d.department_id)

EMPLOYEE_ID LAST_NAME
----------- -------------------------
	205 Higgins
	206 Gietz
	200 Whalen
	100 King
	101 Kochhar
	102 De Haan
	109 Faviet
	108 Greenberg

3.关联子查询

hr@ORCLPDB01 2023-02-26 19:44:47> select last_name,salary,department_id
  2  from employees outer_table
  3  where salary > (
  4                  select avg(salary)
  5                  from employees inner_table
  6                  where inner_table.department_id = outer_table.department_id);

LAST_NAME		      SALARY DEPARTMENT_ID
------------------------- ---------- -------------
King			       24000		90
Hunold				9000		60
Ernst				6000		60
Greenberg		       12008	       100
Faviet				9000	       100

hr@ORCLPDB01 2023-02-26 19:46:38> select e.employee_id,last_name,e.job_id
  2  from employees e 
  3  where 2 <=( select count(*) 
  4              from job_history
  5              where employee_id = e.employee_id);

EMPLOYEE_ID LAST_NAME		      JOB_ID
----------- ------------------------- ----------
	101 Kochhar		      AD_VP
	176 Taylor		      SA_REP
	200 Whalen		      AD_ASST

Elapsed: 00:00:00.00

4.使用exists和not exists操作符

hr@ORCLPDB01 2023-02-26 19:50:39> select employee_id,last_name,job_id,department_id
  2  from employees outer
  3  where exists ( select 'X' 
  4                 from employees
  5                 where manager_id = outer.employee_id);

EMPLOYEE_ID LAST_NAME		      JOB_ID	 DEPARTMENT_ID
----------- ------------------------- ---------- -------------
	100 King		      AD_PRES		    90
	101 Kochhar		      AD_VP		    90
	102 De Haan		      AD_VP		    90
	103 Hunold		      IT_PROG		    60
	108 Greenberg		      FI_MGR		   100
	114 Raphaely		      PU_MAN		    30
	120 Weiss		      ST_MAN		    50
	121 Fripp		      ST_MAN		    50
	122 Kaufling		      ST_MAN		    50
	123 Vollman		      ST_MAN		    50
	124 Mourgos		      ST_MAN		    50
	145 Russell		      SA_MAN		    80
	146 Partners		      SA_MAN		    80
	147 Errazuriz		      SA_MAN		    80
	148 Cambrault		      SA_MAN		    80
	149 Zlotkey		      SA_MAN		    80
	201 Hartstein		      MK_MAN		    20
	205 Higgins		      AC_MGR		   110

18 rows selected.

Elapsed: 00:00:00.01


hr@ORCLPDB01 2023-02-26 19:54:19> select department_id, department_name
  2  from departments d
  3  where not exists ( select 'X' 
  4                     from employees
  5                     where department_id = d.department_id);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
	  120 Treasury
	  130 Corporate Tax
	  140 Control And Credit
	  150 Shareholder Services
	  160 Benefits
	  170 Manufacturing
	  180 Construction
	  190 Contracting
	  200 Operations
	  210 IT Support
	  220 NOC
	  230 IT Helpdesk
	  240 Government Sales
	  250 Retail Sales
	  260 Recruiting
	  270 Payroll

16 rows selected.

Elapsed: 00:00:00.00

6.关联更新

update emp16 e set department_name = (
    select department_name 
    from departments d
    where e.department_id = d.department_id);

7.关联删除

delete from emp16 e
where employee_id = (
    select employee_id 
    from emp_history
    where employee_id = e.employee_id);

6.with子查询