多列子查询

where (manager_id, department_id) in

子查询

100 90

102 60

124 50

主查询的每行都与多行和多列的子查询进行比较

列的比较

多列的比较，包含子查询可以是：

不成对比较

成对比较

成对比较子查询
1、显示与员工名为“John”同部门且同一个经理的其它员工信息

select employee_id, manager_id, department_id from empl_demo

where (manager_id, department_id) IN

(select manager_id, department_id from empl_demo

where first_name = 'John')

AND first_name <> 'John';

不成对比较

1、显示名字不为 “John”员工的经理ID和部门ID的员工号、经理号、部门号

select employee_id, manager_id, department_id

from empl_demo

where manager_id in

(select manager_id

from empl_demo

where first_name = 'john')

and department_id in

(select department_id

from empl_demo

where first_name = 'john')

and first_name <> 'john';

标量子查询表达式

标量子查询是从一行中返回一列的子查询

标量子查询可在下列情况下使用:

– DECODE 和 CASE 条件和表达式的一部分

– SELECT 中除 GROUP BY 子句以外的所有子句中

– UPDATE 语句的 SET 子句和 WHERE 子句

CASE 表达式中的标量子查询：

select employee_id, last_name, department_id,

(case

when department_id =

(select department_id

from departments

where location_id = 1800)

then 'canada' else 'usa' end) location

from employees;

ORDER BY 子句中的标量子查询：

select employee_id, last_name,department_id

from employees e

order by (select department_name

from departments d

where e.department_id = d.department_id);

相关子查询

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

子查询中使用主查询中的列

select column1, column2, ...

from table1 Outer_table

where column1 operator

(selecT column1, column2

from table2

where expr1 = Outer_table.expr2);

2、查找所有的员工信息，谁的薪金超过其部门的平均工资

select last_name, salary, department_id

from employees outer_table

where salary >

(selecT AVG(salary)

from employees inner_table

where inner_table.department_id =

outer_table.department_id);

3、显示哪些员工工作变更过至少两次

select e.employee_id, last_name,e.job_id from employees e

where 2 <= (select count(*) from job_history

where employee_id = e.employee_id);

使用 EXISTS 运算符

EXISTS操作符检查在子查询中是否存在满足条件的行。

如果在子查询中存在满足条件的行:

  – 不在子查询中继续查找

  – 条件返回 TRUE

如果在子查询中不存在满足条件的行:

  – 条件返回 FALSE

  – 继续在子查询中查找

1、使用 EXISTS 操作符查找领导

select employee_id, last_name, job_id, department_id

from employees outer

where exists ( select 'x'

from employees

where manager_id =

outer.employee_id);

查找没有任何员工的部门

select department_id, department_name

from departments d

where not exists (select 'x'

from employees

where department_id = d.department_id);

相关UPDATE

使用相关子查询依据一个表中的数据更新另一个表的数据。

update table1 alias1 set column = (select expression from table2 alias2

where alias1.column = alias2.column);

违反范式的表 EMPL6 添加字段存储部门名称（添加字段以后违反范式）

使用相关子更新填充表

alter table empl6 add(department_name varchar2(25));

update empl6 e

set department_name =

(select department_name

from departments d

where e.department_id = d.department_id);

相关DELETE

使用相关子查询依据一个表中的数据删除另一个表的数据

delete from table1 alias1

where column operator

(select expression

from table2 alias2

where alias1.column = alias2.column);

1、使用相关子查询删除EMPL6存在同时也存在于EMP_HISTORY表中的数据。

delete from empl6 e

where employee_id =

(select employee_id

from emp_history

where employee_id = e.employee_id);

WITH 子句

使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块

WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中

使用 WITH 子句可以提高查询效率

1、使用WITH子句编写一个查询，来显示部门名称和这些部门员工的工资总额大于跨部门的平均工资的部门及工资总额

with

dept_costs as (

select d.department_name, sum(e.salary) as dept_total

from employees e join departments d

on e.department_id = d.department_id

group by d.department_name),

avg_cost as (

select sum(dept_total)/count(*) as dept_avg

from dept_costs)

select *

from dept_costs

where dept_total >

(select dept_avg

from avg_cost)

order by department_name;

递归 WITH 子句

递归WITH子句：

Enables formulation of recursive queries.

Creates query with a name, called the Recursive WITH element name

Contains two types of query blocks member: anchor and a recursive

Is ANSI-compatible

with reachable_from (source, destin, totalflighttime) as

(

select source, destin, flight_time

from flights

union all

select incoming.source, outgoing.destin,

incoming.totalflighttime+outgoing.flight_time

from reachable_from incoming, flights outgoing

where incoming.destin = outgoing.source

)

select source, destin, totalflighttime

from reachable_from;