oracle數(shù)據(jù)庫(kù)-p子查詢

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

高?誰(shuí)的工資比Abel

高?Main

Query:?Abel的工資是多少??Subquery子查詢語(yǔ)法子查詢(內(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

子句。單行操作符對(duì)應(yīng)單行子查詢，多行操作符對(duì)應(yīng)多行子查詢。子查詢類型主查詢子查詢返回ST_CLERK多行子查詢ST_CLERKSA_MAN主查詢子查詢返回單行子查詢單行子查詢只返回一行。使用單行比較操作符。操作符 含義= Equal

to> Greater

than>= Greater

than

orequal

to< Less

than<= Less

than

or

equal

to<> Not

equal

toSELECT

last_name,

job_id,

salaryFROMWHEREemployeesjob_id

=(SELECT

job_idFROM

employeesWHERE employee_id

=

141)ANDsalary

>(SELECT

salaryFROMWHEREemployeesemployee

id

=

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

last_name,

job_id,

salaryFROMWHEREemployeessalary

=(SELECT

MIN(salary)在子查詢中使用組函數(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)SELECT

employee_id,

last_namesalaryFROM

employeesWHERE

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

BY

department

id);ERROR

at

line

4:ORA-01427:

single-row

subquery

returns

more

thanone

row子查詢中的空值問(wèn)題no

rows

selectedSELECT

last_name,

job_idFROM

employeesWHERE job_id

=(SELECT

job_idFROMWHEREemployeeslast_name

=

'Haas');多行子查詢ALL返回多行。使用多行比較操作符。操作符 含義IN

等于列表中的任何一個(gè)ANY

和子查詢返回的任意一個(gè)值比較和子查詢返回的所有值比較在多行子查詢中使用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

<

ALLFROM

employeesANDWHERE job_id

=

'IT_PROG')job_id

<>

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

操作符9000,

6000,

4200(SELECT

salary子查詢中的空值問(wèn)題SELECT

emp.last_nameFROMWHEREemployees

empemp.employee_id

NOT

IN(SELECT

mgr.manager_idFROM employees

mgr);no

rows

selected總結(jié)通過(guò)本章學(xué)習(xí)，您已經(jīng)學(xué)會(huì):在什么時(shí)