目標(biāo)通過本章學(xué)習(xí)，您將可以:描述子查詢可以解決的問題定義子查詢。列句子查詢的類型。書寫單行子查詢和多行字查詢。使用子查詢解決問題誰的工資比Abel

高?誰的工資比Abel

高?MainQuery:?Abel的工資是多少?Subquery?子查詢語法子查詢(內(nèi)查詢)在主查詢之前一次執(zhí)行完成。子查詢的結(jié)果被主查詢使用(外查詢)。SELECTFROMWHEREselect_listtableexpr

operator(SELECTFROMselect_listtable);SELECT

last_nameFROMWHEREemployeessalary

>(SELECT

salaryFROMWHEREemployeeslast_name

=

'Abel');子查詢11000注意事項(xiàng)子查詢要包含在括號(hào)內(nèi)。將子查詢放在比較條件的右側(cè)。除非進(jìn)行Top-N

分析，否則不要在子查詢中使用ORDERBY

子句。單行操作符對應(yīng)單行子查詢，多行操作符對應(yīng)多行子查詢。子查詢類型MainquerySubqueryreturnsST_CLERK多行子查詢ST_CLERKSA_MANMainquerySubqueryreturns單行子查詢單行子查詢只返回一行。使用單行比較操作符。Operator

Meaning= Equal

to> Greater

than>= Greater

than

or

equal

to< Lessthan<= Less

thanor

equal

to<> Not

equal

toSELECT

last_name,

job_id,

salaryFROM

employeesWHERE job_id

=(SELECT

job_idFROM

employeesWHERE employee_id

=

141)ANDsalary

>(SELECT

salaryFROM

employeesWHERE employee

id

=

143);執(zhí)行單行子查詢ST_CLERK2600SELECT

last_name,

job_id,

salaryFROMWHEREemployeessalary

=(SELECT

MIN(salary)FROM

employees);在子查詢中使用組函數(shù)2500子查詢中的HAVING

子句首先執(zhí)行子查詢。向主查詢中的HAVING

子句返回結(jié)果。SELECTFROMdepartment_id,

MIN(salary)employeesGROUP

BY

department_idHAVING MIN(salary)

>FROM

employeesWHERE department_id

=

50);2500(SELECT

MIN(salary)salarySELECT

employee_id,

last_nameFROM

employeesWHERE

=(SELECTFROMMIN(salary)employees使用子查詢GROUP

BY

department

id);ERROR

at

line

4:ORA-01427:

single-row

subquery

returns

more

thanone

row子查詢中的空值問題no

rows

selectedSELECT

last_name,

job_idFROM

employeesWHERE job_id

=(SELECT

job_idFROMWHEREemployeeslast_name

=

'Haas');多行子查詢ANYALL返回多行。使用多行比較操作符。Operator

MeaningIN

Equal

to

any

member

in

the

listCompare

value

to

each

value

returnedbythe

subquery

只要滿足其中至少一個(gè)條件Compare

value

to

every

value

returnedbythe

subquery

必須滿足其中所有條件在多行子查詢中使用ANY

操作符9000,

6000,

4200SELECT

employee_id,

last_name,

job_id,

salaryFROMWHEREemployeessalary

<ANY(SELECT

salaryFROM

employeesWHERE job_id

=

'IT_PROG')ANDjob_id

<>

'IT_PROG';…SELECT

employee_id,

last_name,

job_id,

salaryFROM

employeesWHERE salary

<ALL(SELECT

salaryFROM

employeesWHERE job_id

=

'IT_PROG')ANDjob_id

<>

'IT_PROG';在多行子查詢中使用ALL

操作符9000,

6000,

4200子查詢中的空值問題SELECT

emp.last_nameFROMWHEREemployees

empemp.employee_id

NOT

IN(SELECT

mgr.manager_idFROM employees

mgr);no

rows

selected總結(jié)通過本章學(xué)習(xí)，您已經(jīng)學(xué)會(huì):在什么時(shí)候遇到什么問題應(yīng)該使用子查詢。在查詢是基于未知的值時(shí)應(yīng)使用子查詢。SELECTFROMWHEREselect_listtableexpr

operator(SELECT

select_listFROM

table);Practice6

OverviewThis

practicecovers

thefollowing

topics:Creating

subqueries

to