SQL 基礎之使用子查詢檢索資料（二十二）

多列子查詢

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

where first_name = 'john')

and department_id in

(select department_id

and first_name <> 'john';

标量子查詢表達式

标量子查詢是從一行中傳回一列的子查詢

标量子查詢可在下列情況下使用:

– DECODE 和 CASE 條件和表達式的一部分

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

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

CASE 表達式中的标量子查詢：

select employee_id, last_name, department_id,

(case

when 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);

相關子查詢

相關子查詢按照一行接一行的順序執行，主查詢的每一行都執行一次子查詢

<a href="https://s2.51cto.com/wyfs02/M02/8F/3F/wKioL1jY0ZPDp46NAABXUTDyPS4665.jpg" target="_blank"></a>

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

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 &gt;

(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 &lt;= (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

where not exists (select 'x'

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

相關DELETE

使用相關子查詢依據一個表中的資料删除另一個表的資料

delete from table1 alias1

where column operator

(select expression

from table2 alias2

1、使用相關子查詢删除EMPL6存在同時也存在于EMP_HISTORY表中的資料。

delete from empl6 e

where employee_id =

(select employee_id

from emp_history

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 &gt;

(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;

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1910824，如需轉載請自行聯系原作者