1、,Advanced Subqueries,Objectives,After completing this lesson, you should be able to do the following:Write a multiple-column subqueryDescribe and explain the behavior of subqueries when null values are retrievedWrite a subquery in a FROM clauseUse scalar subqueries in SQLDescribe the types of proble
2、ms that can be solved with correlated subqueriesWrite correlated subqueriesUpdate and delete rows using correlated subqueriesUse the EXISTS and NOT EXISTS operatorsUse the WITH clause,What Is a Subquery?,A subquery is a SELECT statement embedded in a clause of another SQL statement.,SELECT . FROM .W
3、HERE .,(SELECT . FROM . WHERE .),Mainquery,Subquery,Subqueries,The subquery (inner query) executes once before the main query.The result of the subquery is used by the main query (outer query).,SELECTselect_listFROMtableWHEREexpr operator (SELECT select_list FROM table);,SELECT last_nameFROM employe
4、esWHERE salary (SELECT salary FROM employees WHERE employee_id = 149) ;,Using a Subquery,Multiple-Column Subqueries,Main query,WHERE (MANAGER_ID, DEPARTMENT_ID) IN,Subquery,100 90102 60124 50,Each row of the main query is compared to values from a multiple-row and multiple-column subquery.,Column Co
5、mparisons,Column comparisons in a multiple-column subquery can be: Pairwise comparisons Nonpairwise comparisons,Pairwise Comparison Subquery,Display the details of the employees who are managed by the same manager and work in the same department as the employees with EMPLOYEE_ID 178 or 174.,SELECTem
6、ployee_id, manager_id, department_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);,Nonpairwise Comparison Subquery,SELECT employee_id, manager_id, department_idFROM employeesWHERE manag
7、er_id IN (SELECT manager_id FROM employees WHERE employee_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);,Display the details of the employees who are managed by the same manager as the employees with EMPLOYEE_ID
8、174 or 141 and work in the same department as the employees with EMPLOYEE_ID 174 or 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.department_id = b.department_idAND a.salary b.sa
9、lavg;,Using a Subquery in the FROM Clause,Scalar Subquery Expressions,A scalar subquery expression is a subquery thatreturns exactly one column value from one row.Scalar subqueries were supported in Oracle8i only in a limited set of cases, For example:SELECT statement (FROM and WHERE clauses)VALUES
10、list of an INSERT statementIn Oracle9i, scalar subqueries can be used in:Condition and expression part of DECODE and CASEAll clauses of SELECT except GROUP BY,Scalar Subqueries: Examples,Scalar Subqueries in CASE Expressions,SELECT employee_id, last_name, (CASE WHEN department_id = THEN Canada ELSE
11、USA END) locationFROM employees;,(SELECT department_id FROM departmentsWHERE location_id = 1800),Scalar Subqueries in ORDER BY Clause,SELECT employee_id, last_nameFROM employees eORDER BY,(SELECT department_name FROM departments d WHERE e.department_id = d.department_id);,Hidden Slide,Correlated Sub
12、queries,Correlated subqueries are used for row-by-rowprocessing. Each subquery is executed once forevery row of the outer query.,GETcandidate row from outer query,EXECUTEinner query using candidate row value,USEvalues from inner query to qualify or disqualify candidate row,Correlated Subqueries,SELE
13、CT column1, column2, . FROM table1 WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = .expr2);,The subquery references a column from a table in the parent query.,outer,outer,SELECT last_name, salary, department_idFROM employees outerWHERE salary ,Using Correlated Subqueries,Eac
14、h time a row from the outer queryis processed, theinner query isevaluated.,Find all employees who earn more than the average salary in their department.,(SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id) ;,Using Correlated Subqueries,SELECT e.employee_id, last_name,e.job_i
15、dFROM employees e WHERE 2 (SELECT dept_avg FROM avg_cost)ORDER BY department_name;,Summary,In this lesson, you should have learned the following: A multiple-column subquery returns more than one column.Multiple-column comparisons can be pairwise or nonpairwise.A multiple-column subquery can also be
16、used in the FROM clause of a SELECT statement.Scalar subqueries have been enhanced inOracle9i.,Summary,Correlated subqueries are useful whenever a subquery must return a different result for each candidate row.The EXISTS operator is a Boolean operator that tests the presence of a value.Correlated su
17、bqueries can be used with SELECT, UPDATE, and DELETE statements.You can use the WITH clause to use the same query block in a SELECT statement when it occurs more than once,Practice 18 Overview,This practice covers the following topics:Creating multiple-column subqueriesWriting correlated subqueriesUsing the EXISTS operatorUsing scalar subqueriesUsing the WITH clause,
