Week 14 - Subquery PDF

INFMGMT INFMGMT –– INFORMATION INFORMATION MANAGEMENT MANAGEMENT SUBQUERIES  OBJECTIVES After After completing completing this this lesson, lesson, you...

INFMGMT INFMGMT –– INFORMATION INFORMATION MANAGEMENT MANAGEMENT SUBQUERIES  OBJECTIVES After After completing completing this this lesson, lesson, you you should should be be able able to to do do the the following: following: Describe Describe the the types types ofof problems problems that that subqueries subqueries can can solve solve Define Define subqueries subqueries List List the the types types of of subqueries subqueries Write Write single-row single-row and and multiple-row multiple-row subqueries subqueries  USING A SUBQUERY TO SOLVE A PROBLEM “Who “Who has has a a salary salary greater greater than than Jones’?” Jones’?” Main Query “Which employees have a salary greater ? than Jones’ salary?” Subquery ? “What is Jones’ salary?”  SUBQUERIES SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); The The subquery subquery (inner (inner query) query) executes executes once once before before the the main query. main query. The The result result of of the the subquery subquery is is used used by by the the main main query query (outer query). (outer query). Subqueries A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses: WHERE clause HAVING clause FROM clause  USING A SUBQUERY SQL> SELECT ename 2 FROM emp 2975 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno=7566); In the example, the inner query determines the salary of employee 7566. The outer query takes the result of the inner query and uses this result to display all the employees who earn more than this amount.  GUIDELINES FOR USING SUBQUERIES Enclose Enclose subqueries subqueries in in parentheses. parentheses. Place Place subqueries subqueries on on the the right right side side of of the the comparison comparison operator. operator. Do Do not not add add an an ORDER ORDER BYBY clause clause to to aa subquery. subquery. Use Use single-row single-row operators operators with with single-row single-row subqueries. subqueries. Use Use multiple-row multiple-row operators operators with with multiple-row multiple-row subqueries. subqueries.  TYPES OF SUBQUERIES Single-row subquery :: Queries Single-row subquery Queries that that return return only only one one row row from from the the inner inner SELECT SELECT statement statement Main query returns Subquery CLERK Multiple-row Multiple-row subquery subquery Queries Queries that that return return more more than than one one row row from from the the inner inner SELECT SELECT statement statement Main query Subquery returns CLERK MANAGER Multiple-column subqueryQueries Multiple-column subqueryQueries that that return return more more than than one one column column from from the the inner inner SELECT SELECT statement statement Main query returns CLERK 7900 Subquery MANAGER 7698  SINGLE-ROW Return Return only only one one row Use Use single-row row single-row comparison SUBQUERIES comparison operators operators Operator Meaning = Equal to > Greater than >= Greater than or equal to < Less than SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE empno = 7369); OUTP UT: ENAME JOB ---------- --------- JAMES CLERK SMITH CLERK ADAMS CLERK MILLER CLERK  EXECUTING SINGLE-ROW SUBQUERIES The example on the slide displays employees whose job title is the same as that of employee 7369 and whose salary is greater than that of employee 7876. SQL> SELECT ename, job 2 FROM emp 3 WHERE job = CLERK 4 (SELECT job 5 FROM emp 6 WHERE empno = 7369) 7 AND sal > 1100 8 (SELECT sal 9 FROM emp 10 WHERE empno = 7876); OUTP UT: ENAME ENAME JOB JOB ---------- ---------- --------- --------- MILLER MILLER CLERK CLERK  USING GROUP FUNCTIONS IN A SUBQUERY Display the employee name, job title, and salary of all employees whose salary is equal to the minimum salary. SQL> SELECT ename, job, sal 2 FROM emp 800 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp); OUTP UT: ENAME JOB SAL ENAME JOB SAL ---------- ---------- --------- --------- --------- --------- SMITH SMITH CLERK CLERK 800 800  HAVING CLAUSE WITH SUBQUERIES The The Oracle Oracle Server Server executes executes subqueries subqueries first. first. The The Oracle Oracle Server Server returns returns results results into into the the HAVING HAVING clause clause of of the the main main query. query. The SQL statement on the slide displays all the departments that have a minimum salary greater than that of department 20. SQL> SELECT deptno, MIN(sal) 2 FROM emp 3 GROUP BY deptno 800 4 HAVING MIN(sal) > 5 (SELECT MIN(sal) 6 FROM emp 7 WHERE deptno = 20);  WHAT IS WRONG WITH THIS STATEMENT? SQL> SELECT empno, ename 2 FROM emp 3 WHERE sal = 4 i th (SELECT MIN(sal) w 5 p er ator y FROM emp o r 6gle -row subque GROUP BY deptno); S i n ow l t i p le -r mu ERROR: ERROR: ORA-01427: ORA-01427: single-row single-row subquery subquery returns returns more more than than one one row row no no rows rows selected selected Errors with Subqueries One common error with subqueries is more than one row returned for a single-row subquery. In the SQL statement on the slide, the subquery contains a GROUP BY (deptno) clause, which implies that the subquery will return multiple rows, one for each group it finds. In this case, the result of the subquery will be 800, 1300, and 950. The outer query takes the results of the subquery (800, 950, 1300) and uses these results in its WHERE clause. The WHERE clause contains an equal (=) operator, a single-row comparison operator expecting only one value. The = operator cannot accept more than one value from the subquery and hence generates the error. To correct this error, change the = operator to IN.  WILL THIS STATEMENT WORK? SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename='SMYTHE'); l u es a n ov no no rows rows selected t u rns selected r y re b q ue Su Problems with Subqueries A common problem with subqueries is no rows being returned by the inner query. In the SQL statement on the slide, the subquery contains a WHERE (ename='SMYTHE') clause. Presumably, the intention is to find the employee whose name is Smythe. The statement seems to be correct but selects no rows when executed. The problem is that Smythe is misspelled. There is no employee named Smythe. So the subquery returns no rows. The outer query takes the results of the subquery (null) and uses these results in its WHERE clause. The outer query finds no employee with a job title equal to null and so returns no rows.  MULTIPLE-ROW SUBQUERIES Return Return more more than than one one row row Use Use multiple-row multiple-row comparison comparison operators operators Operator Meaning IN Equal to any member in the list ANY Compare value to each value returned by the subquery Compare value to every value returned by ALL the subquery USING USING ANY ANY OPERATOR OPERATOR IN IN MULTIPLE-ROW MULTIPLE-ROW SUBQUERIES SUBQUERIES Display the employees whose salary is less than any clerk and who are not clerks. SQL> SELECT empno, ename, job 1300 2 FROM emp 1100 800 3 WHERE sal < ANY 950 4 (SELECT sal 5 FROM emp 6 WHERE job = 'CLERK') 7 AND job 'CLERK'; OUTP UT: EMPNO EMPNO ENAME ENAME JOB JOB --------- --------- ---------- ---------- --------- --------- 7654 7654 MARTIN MARTIN SALESMAN SALESMAN 7521 7521 WARD WARD SALESMAN SALESMAN ANY means more than the minimum. =ANY is equivalent to IN.  USING ALL OPERATOR IN IN MULTIPLE-ROW MULTIPLE-ROW SUBQUERIES SUBQUERIES Display the employees whose salary is greater than the average salaries of all the departments. SQL> SELECT empno, ename, job 1566.6667 2 FROM emp 2175 2916.6667 3 WHERE sal > ALL 4 (SELECT avg(sal) 5 FROM emp 6 GROUP BY deptno); OUTP UT: EMPNO EMPNO ENAME ENAME JOB JOB --------- --------- ---------- ---------- --------- --------- 7839 7839 KING KING PRESIDENT PRESIDENT 7566 7566 JONES JONES MANAGER MANAGER 7902 7902 FORD FORD ANALYST ANALYST 7788 7788 SCOTT SCOTT ANALYST ANALYST >ALL means more than the maximum and