高級子查詢通過本章學(xué)習(xí)您將可以

1、高級子查詢 目標(biāo)通過本章學(xué)習(xí)，您將可以:書寫多列子查詢子查詢對空值的處理在 FROM 子句中使用子查詢在SQL中使用單列子查詢相關(guān)子查詢書寫相關(guān)子查詢使用子查詢更新和刪除數(shù)據(jù)使用 EXISTS 和 NOT EXISTS 操作符使用 WITH 子句子查詢子查詢是嵌套在 SQL 語句中的另一個(gè)SELECT 語句SELECT . FROM .WHERE .(SELECT . FROM . WHERE .)主查詢子查詢子查詢子查詢 (內(nèi)查詢) 在主查詢執(zhí)行之前執(zhí)行主查詢使用子查詢的結(jié)果 (外查詢)SELECTselect_listFROMtableWHEREexpr operator (SELECT

2、select_list FROM table);SELECT last_nameFROM employeesWHERE salary (SELECT salary FROM employees WHERE employee_id = 149) ;子查詢應(yīng)用舉例10500多列子查詢Main queryWHERE (MANAGER_ID, DEPARTMENT_ID) INSubquery100 90102 60124 50主查詢與子查詢返回的多個(gè)列進(jìn)行比較列比較多列子查詢中的比較分為兩種: 成對比較不成對比較成對比較舉例SELECTemployee_id, manager_id, departm

3、ent_idFROMemployeesWHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (178,174)ANDemployee_id NOT IN (178,174);不成對比較舉例SELECT employee_id, manager_id, department_idFROM employeesWHERE manager_id IN (SELECT manager_id FROM employees WHERE employe

4、e_id IN (174,141)AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)ANDemployee_id NOT IN(174,141);SELECT a.last_name, a.salary, a.department_id, b.salavgFROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) bWHERE a.d

5、epartment_id = b.department_idAND a.salary b.salavg;在 FROM 子句中使用子查詢單列子查詢表達(dá)式單列子查詢表達(dá)式是在一行中只返回一列的子查詢Oracle8i 只在下列情況下可以使用, 例如:SELECT 語句 (FROM 和 WHERE 子句)INSERT 語句中的VALUES列表中Oracle9i中單列子查詢表達(dá)式可在下列情況下使用:DECODE 和 CASESELECT 中除 GROUP BY 子句以外的所有子句中單列子查詢應(yīng)用舉例在 CASE 表達(dá)式中使用單列子查詢SELECT employee_id, last_name, (CAS

6、E WHEN department_id = THEN Canada ELSE USA END) locationFROM employees; (SELECT department_id FROM departmentsWHERE location_id = 1800)在 ORDER BY 子句中使用單列子查詢SELECT employee_id, last_nameFROM employees eORDER BY 20(SELECT department_name FROM departments d WHERE e.department_id = d.department_id);相關(guān)子

7、查詢 相關(guān)子查詢按照一行接一行的順序執(zhí)行，主查詢的每一行都執(zhí)行一次子查詢GET從主查詢中獲取候選列EXECUTE子查詢使用主查詢的數(shù)據(jù)USE如果滿足內(nèi)查詢的條件則返回該行相關(guān)子查詢 SELECT column1, column2, . FROM table1 WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = .expr2);子查詢中使用主查詢中的列outerouterSELECT last_name, salary, department_idFROM employees outerWHERE salary 相關(guān)子查詢舉例(SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id) ;相關(guān)子查詢舉例SELECT e.employee_id, last_name,e.job_idFROM employees e WHERE 2 (SELECT dept_avg FROM avg_cost)ORDER BY department_name;總結(jié)通過本章學(xué)習(xí),您已經(jīng)可以: 使用多列子