Types of Subqueries Singlerow subquery Main query Subquery
Types of Subqueries • Single-row subquery Main query Subquery returns CLERK • Multiple-row subquery Main query Subquery returns CLERK MANAGER • Multiple-column subquery Main query Subquery 6 -1 returns CLERK 7900 MANAGER 7698 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Single-Row Subqueries • Return only one row • Use single-row comparison operators Operator 6 -2 Meaning = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to Copyright Ó Oracle Corporation, 1998. All rights reserved.
Executing Single-Row Subqueries SQL> 2 3 4 5 6 7 8 9 10 SELECT FROM WHERE AND ename, job emp job = (SELECT FROM WHERE sal > (SELECT FROM WHERE CLERK job empno = 7369) 1100 sal empno = 7876); ENAME JOB -----MILLER CLERK 6 -3 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Multiple-Row Subqueries • Return more than one row • Use multiple-row comparison operators Operator IN Equal to any member in the list ANY Compare value to each value returned by the subquery ALL 6 -4 Meaning Compare value to every value returned by the subquery Copyright Ó Oracle Corporation, 1998. All rights reserved.
Using Multiple-Column Subqueries Display the order number, product number, and quantity of any item in which the product number and quantity match both the product number and quantity of an item in order 605. SQL> 2 3 4 5 6 7 6 -5 SELECT ordid, prodid, qty FROM item WHERE (prodid, qty) IN (SELECT prodid, qty FROM WHERE AND ordid <> 605; item ordid = 605) Copyright Ó Oracle Corporation, 1998. All rights reserved.
Column Comparisons Pairwise PRODID 101863 100861 102130 100890 100870 101860 6 -6 Nonpairwise QTY 100 10 5 500 50 PRODID 101863 100861 102130 100890 100870 101860 Copyright Ó Oracle Corporation, 1998. All rights reserved. QTY 100 10 5 500 50
Nonpairwise Comparison Subquery Display the order number, product number, and quantity of any item in which the product number and quantity match any product number and any quantity of an item in order 605. SQL> 2 3 4 5 6 7 8 9 6 -7 SELECT FROM WHERE AND ordid, prodid, qty item prodid IN (SELECT FROM WHERE qty IN (SELECT FROM WHERE ordid <> 605; prodid item ordid = 605) qty item ordid = 605) Copyright Ó Oracle Corporation, 1998. All rights reserved.
Null Values in a Subquery SQL> SELECT employee. ename 2 FROM employee 3 WHERE employee. empno NOT IN 4 (SELECT manager. mgr 5 FROM emp manager); no rows selected. 6 -8 Copyright Ó Oracle Corporation, 1998. All rights reserved.
Using a Subquery in the FROM Clause SQL> 2 3 4 5 6 SELECT FROM WHERE AND a. ename, a. sal, a. deptno, b. salavg emp a, (SELECT deptno, avg(sal) salavg FROM emp GROUP BY deptno) b a. deptno = b. deptno a. sal > b. salavg; ENAME SAL DEPTNO SALAVG ----------KING 5000 10 2916. 6667 JONES 2975 20 2175 SCOTT 3000 20 2175. . . 6 rows selected. 6 -9 Copyright Ó Oracle Corporation, 1998. All rights reserved.
- Slides: 9