高級子查詢通過本章學習您將可以

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

2、select_list FROM table);SELECT last_nameFROM employeesWHERE salary (SELECT salary FROM employees WHERE employee_id = 149) ;子查詢應用舉例10500多列子查詢Main queryWHERE (MANAGER_ID, DEPARTMENT_ID) INSubquery100 90102 60124 50主查詢與子查詢返回的多個列進行比較列比較多列子查詢中的比較分為兩種: 成對比較不成對比較成對比較舉例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 子句中使用子查詢單列子查詢表達式單列子查詢表達式是在一行中只返回一列的子查詢Oracle8i 只在下列情況下可以使用, 例如:SELECT 語句 (FROM 和 WHERE 子句)INSERT 語句中的VALUES列表中Oracle9i中單列子查詢表達式可在下列情況下使用:DECODE 和 CASESELECT 中除 GROUP BY 子句以外的所有子句中單列子查詢應用舉例在 CASE 表達式中使用單列子查詢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);相關子

7、查詢 相關子查詢按照一行接一行的順序執(zhí)行，主查詢的每一行都執(zhí)行一次子查詢GET從主查詢中獲取候選列EXECUTE子查詢使用主查詢的數(shù)據(jù)USE如果滿足內查詢的條件則返回該行相關子查詢 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 相關子查詢舉例(SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id) ;相關子查詢舉例SELECT e.employee_id, last_name,e.job_idFROM employees e WHERE 2 (SELECT dept_avg FROM avg_cost)ORDER BY department_name;總結通過本章學習,您已經(jīng)可以: 使用多列子