數(shù)據(jù)庫(kù)技術(shù)與Oracle：sql-06 Subqueries

1、6Subqueries 6-2ObjectivesAfter completing this lesson, you should be able to do the following:Describe the types of problem that subqueries can solveDefine subqueriesList the types of subqueriesWrite single-row and multiple-row subqueries6-3Using a Subquery to Solve a ProblemWho has a salary greater

2、 than Abels?Which employees have salaries greater than Abels salary?Main Query:What is Abels salary?Subquery6-4Subquery SyntaxThe 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

3、 (SELECTselect_list FROMtable);6-5SELECT last_nameFROM employeesWHERE salary (SELECT salary FROM employees WHERE last_name = Abel);Using a Subquery110006-6Guidelines for Using SubqueriesEnclose subqueries in parentheses. Place subqueries on the right side of the comparison condition.The ORDER BY cla

4、use in the subquery is not needed unless you are performing top-n analysis.Use single-row operators with single-row subqueries and use multiple-row operators withmultiple-row subqueries.6-7Types of SubqueriesMain querySubqueryreturnsST_CLERK Multiple-row subqueryST_CLERKSA_MANMain querySubqueryretur

5、ns Single-row subquery6-8Single-Row SubqueriesReturn only one rowUse single-row comparison operatorsOperator= = =MeaningEqual toGreater than Greater than or equal to Less than Less than or equal toNot equal to6-9 SELECT last_name, job_id, salaryFROM employeesWHERE job_id = (SELECT job_id FROM employ

6、ees WHERE employee_id = 141)AND salary (SELECT salary FROM employees WHERE employee_id = 143);Executing Single-Row SubqueriesST_CLERK26006-10SELECT last_name, job_id, salaryFROM employeesWHERE salary = (SELECT MIN(salary) FROM employees);Using Group Functions in a Subquery25006-11The HAVING Clause w

7、ith SubqueriesThe Oracle server executes subqueries first.The Oracle server returns results into the HAVING clause of the main query.SELECT department_id, MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) (SELECT MIN(salary) FROM employees WHERE department_id = 50);25006-12SELECT emp

8、loyee_id, last_nameFROM employeesWHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);What Is Wrong With This Statement?ERROR at line 4:ORA-01427: single-row subquery returns more thanone rowSingle-row operator with multiple-row subquery6-13Will This Statement Return Rows?no row

9、s selectedSELECT last_name, job_idFROM employeesWHERE job_id = (SELECT job_id FROM employees WHERE last_name = No);Subquery returns no values6-14Multiple-Row SubqueriesReturn more than one rowUse multiple-row comparison operatorsOperator IN ANY ALLMeaningEqual to any member in the listCompare value

10、to each value returned by the subquery Compare value to every value returned by the subquery 6-15Using the ANY Operator in Multiple-Row Subqueries9000, 6000, 4200SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary ANY (SELECT salary FROM employees WHERE job_id = IT_PROG)AND job_i

11、d IT_PROG;6-16SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary ALL (SELECT salary FROM employees WHERE job_id = IT_PROG)AND job_id IT_PROG;Using the ALL Operator in Multiple-Row Subqueries9000, 6000, 4200 6-17Null Values in a SubquerySELECT emp.last_nameFROM employees empWHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr);6-18SummaryIn this lesson, you should have learned how to:Ide