7 MultipleColumn Subqueries Copyright Oracle Corporation 1998 All

7 Multiple-Column Subqueries Copyright Ó Oracle Corporation, 1998. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Write a multiple-column subquery • Describe and explain the behavior of subqueries when null values are retrieved • Write a subquery in a FROM clause 7 -2 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Multiple-Column Subqueries Main query MANAGER 10 Subquery SALESMAN MANAGER CLERK Main query compares MANAGER 10 7 -3 to 30 10 20 Values from a multiple-row and multiple-column subquery SALESMAN MANAGER CLERK Copyright Ó Oracle Corporation, 1998. All rights reserved. 30 10 20

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 7 -4 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.

Pair wise select ename, deptno, sal from emp where (deptno, sal) in (select deptno, sal from emp where comm is not null); 7 -6 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Column Comparisons Pairwise PRODID 101863 100861 102130 100890 100870 101860 7 -7 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 7 -8 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.

Non Pair wise select ename, deptno, sal from emp where deptno in (select deptno from emp where comm is not null) and sal in (select sal from emp where comm is not null); 7 -9 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Nonpairwise Subquery ORDID PRODID QTY ---------609 100870 5 616 100861 10 616 102130 10 621 100861 10 618 100870 10 618 100861 50 616 100870 50 617 100861 100 619 102130 100 615 100870 100 617 101860 100 621 100870 100 617 102130 100. . . 16 rows selected. 7 -10 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. 7 -11 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. 7 -12 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Correlated Subquery The outer Query is executed first and then the inner query is executed. Find the employee list who earn more than the avg salary of their own department Example: Select * from emp X Where sal > (Select avg(sal) from emp where x. deptno=deptno) 7 -13 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Exists operator Find the list of employees who has subordinates. Select * from emp e Where exists(select 'X' from emp Where mgr=e. empno); 7 -14 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Exists alternative Select * from emp Where empno in (select mgr from emp where mgr is not null); select distinct a. ename from emp a, emp b where a. empno=b. mgr; select empno from emp where empno in (select mgr from emp); 7 -15 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Not Exists Find the list of employees who has manager Select * from emp e Where not exists(select 'X' from emp Where mgr=e. empno) 7 -16 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Non Exists Alternative Select * from emp Where empno not in (select mgr from emp where mgr is not null) 7 -17 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Summary • A multiple-column subquery returns more than one column. • Column comparisons in multiplecolumn comparisons can be pairwise or nonpairwise. • A multiple-column subquery can also be used in the FROM clause of a SELECT statement. 7 -18 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Practice Overview Creating multiple-column subqueries 7 -19 Copyright Ó Oracle Corporation, 1998. All rights reserved.