6 Subqueries Copyright Oracle Corporation 1998 All rights

6 Subqueries Copyright Ó Oracle Corporation, 1998. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Describe the types of problems that subqueries can solve • Define subqueries • List the types of subqueries • Write single-row and multiple-row subqueries 6 -2 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Using a Subquery to Solve a Problem “Who has a salary greater than Jones’? ” Main Query ? “Which employees have a salary greater than Jones’ salary? ” Subquery ? 6 -3 “What is Jones’ salary? ” Copyright Ó Oracle Corporation, 1998. All rights reserved.

Subqueries SELECT FROM WHERE select_list table expr operator (SELECT FROM select_list table); • The subquery (inner query) executes once before the main query. • The result of the subquery is used by the main query (outer query). 6 -4 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Using a Subquery SQL> SELECT ename 2 FROM emp 2975 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno=7566); ENAME -----KING FORD SCOTT 6 -5 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Guidelines for Using Subqueries • Enclose subqueries in parentheses. • Place subqueries on the right side of the comparison operator. • Do not add an ORDER BY clause to a subquery. • Use single-row operators with singlerow subqueries. • Use multiple-row operators with multiple -row subqueries. 6 -6 Copyright Ó Oracle Corporation, 1998. All rights reserved.

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 -7 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 -8 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 -9 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Using Group Functions in a Subquery SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp); 800 ENAME JOB SAL ----- ----SMITH CLERK 800 6 -10 Copyright Ó Oracle Corporation, 1998. All rights reserved.

HAVING Clause with Subqueries • The Oracle Server executes subqueries first. • The Oracle Server returns results into the HAVING clause of the main query. SQL> 2 3 4 5 6 7 6 -11 SELECT FROM GROUP BY HAVING deptno, MIN(sal) emp deptno MIN(sal) > (SELECT FROM WHERE 800 MIN(sal) emp deptno = 20); Copyright Ó Oracle Corporation, 1998. All rights reserved.

What Is Wrong with This Statement? SQL> SELECT empno, ename 2 FROM emp 3 WHERE sal = 4 (SELECT h t i r w FROM o 5 t a er p y o 6 w quer GROUP BY bq -ro u e l s g Sin ple-row i t l u m MIN(sal) emp deptno); ERROR: ORA-01427: single-row subquery returns more than one row no rows selected 6 -12 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Will This Statement Work? SQL> SELECT ename, 2 FROM emp 3 WHERE job = 4 5 6 job (SELECT job FROM emp WHERE ename='SMYTHE'); no rows selected ry e u q b u S 6 -13 es u l va o n s n r u t e r 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 -14 Meaning Compare value to every value returned by the subquery Copyright Ó Oracle Corporation, 1998. All rights reserved.

Using ANY Operator in Multiple-Row Subqueries SQL> 2 3 4 5 6 7 SELECT FROM WHERE AND EMPNO ----7654 7521 6 -15 empno, ename, job 1300 1100 emp 800 sal < ANY 950 (SELECT sal FROM emp WHERE job = 'CLERK') job <> 'CLERK'; ENAME -----MARTIN WARD JOB ----SALESMAN Copyright Ó Oracle Corporation, 1998. All rights reserved.

Using ALL Operator in Multiple-Row Subqueries SQL> SELECT 2 FROM 3 WHERE 4 5 6 EMPNO ----7839 7566 7902 7788 6 -16 empno, ename, job 1566. 6667 2175 emp 2916. 6667 sal > ALL (SELECT avg(sal) FROM emp GROUP BY deptno); ENAME -----KING JONES FORD SCOTT JOB ----PRESIDENT MANAGER ANALYST Copyright Ó Oracle Corporation, 1998. All rights reserved.

Summary Subqueries are useful when a query is based on unknown values. SELECT FROM WHERE 6 -17 select_list table expr operator (SELECT select_list FROM table); Copyright Ó Oracle Corporation, 1998. All rights reserved.

Practice Overview • Creating subqueries to query values based on unknown criteria • Using subqueries to find out what values exist in one set of data and not in another 6 -18 Copyright Ó Oracle Corporation, 1998. All rights reserved.