Listing and Writing Different Types of Different Types of Sub queries
The following list identifies several different types of sub queries you may need to understand and use on the OCP exam:
- Single-row subqueries The main query expects the subquery to return only one value.
- Multirow subqueries The main query can handle situations where the subquery returns more than one value.
- Multiple-column subqueries A subquery that contains more than one column of return data in addition to however many rows are given in the output. These types of subqueries will be discussed later in the chapter.
- Inline views A subquery in a from clause used for defining an intermediate result set to query from. These types of subqueries will be discussed later in the chapter.
Single-Row Subqueries
The main query expects the sub query to return only one value.
Check out the following example, which should look familiar:
SQL> select ename, deptno, sal
2 from emp
3 where deptno =
4 ( select deptno
5 from dept
6 where loc = 'NEW YORK' );
Check out the following example, which should look familiar:
SQL> select ename, deptno, sal
2 from emp
3 where deptno =
4 ( select deptno
5 from dept
6 where loc = 'NEW YORK' );
ENAME DEPTNO SAL
---------- --------- ---------
CLARK 10 2450
KING 10 5000
MILLER 10 1300
---------- --------- ---------
CLARK 10 2450
KING 10 5000
MILLER 10 1300
Though the above query results have 3 rows it is a single-row subquery Because, the subquery on the DEPT table to derive the output from EMP returns only one row of data.
Multi row subquery
A multi row subquery returns one or more rows. Since it returns multiple values, the query must use the set comparison operators (IN,ALL,ANY). If you use a multi row subquery with the equals comparison operators, the database will return an error if more than one row is returned.
Exampe:
select last_name from employees where manager_id in
(select employee_id from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id='UK')));
with
You can improve the performance of this query by having Oracle9i execute the subquery only once, then simply letting Oracle9i reference it at the appropriate points in the main query. The following code block gives a better logical idea of the work Oracle must perform to give you the result. In it, the bold text represents the common parts of the subquery that are performed only once, and the places where the subquery is referenced:
SQL> with summary as
2 (select dname, sum(sal) as dept_total
3 from emp, dept
4 where emp.deptno = dept.deptno
5 group by dname)
6 select dname, dept_total
7 from summary
8 where dept_total >
9 (select sum(dept_total) * 1/3
10 from summary)
11 order by dept_total desc;
DNAME DEPT_TOTAL
-------------------- ----------
RESEARCH 10875
Exampe:
select last_name from employees where manager_id in
(select employee_id from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id='UK')));
with
You can improve the performance of this query by having Oracle9i execute the subquery only once, then simply letting Oracle9i reference it at the appropriate points in the main query. The following code block gives a better logical idea of the work Oracle must perform to give you the result. In it, the bold text represents the common parts of the subquery that are performed only once, and the places where the subquery is referenced:
SQL> with summary as
2 (select dname, sum(sal) as dept_total
3 from emp, dept
4 where emp.deptno = dept.deptno
5 group by dname)
6 select dname, dept_total
7 from summary
8 where dept_total >
9 (select sum(dept_total) * 1/3
10 from summary)
11 order by dept_total desc;
DNAME DEPT_TOTAL
-------------------- ----------
RESEARCH 10875
Multiple-Column Subqueries
Notice that in all the prior examples, regardless of whether one row or multiple rows were returned from the sub query, each of those rows contained only one column's worth of data to compare at the main query level. The main query can be set up to handle multiple columns in each row returned, too. To evaluate how to use multiple-column sub queries, let's consider an example
Select *
From PO_LINES_ALL
Where (PO_HEADER_ID, PO_LINE_ID) IN
(
Select PO_HEADER_ID, PO_LINE_ID
From PO_LINE_LOCATIONS_ALL
WHERE QUANTITY_RECEIVED < QUANTITY/2
AND CLOSED_CODE <> 'CLOSED FOR RECEIVING'
)
The benefit of writing query in above format is that separating the requirements in tables. From PO_LINE_LOCATIONS_ALL we are only taking those data which are relevant for our purpose and our end aim is to view the PO_LINEA_ALL entries corresponding to some required conditions satisfied by entries in PO_LINE_LOCATIONS_AL
From PO_LINES_ALL
Where (PO_HEADER_ID, PO_LINE_ID) IN
(
Select PO_HEADER_ID, PO_LINE_ID
From PO_LINE_LOCATIONS_ALL
WHERE QUANTITY_RECEIVED < QUANTITY/2
AND CLOSED_CODE <> 'CLOSED FOR RECEIVING'
)
The benefit of writing query in above format is that separating the requirements in tables. From PO_LINE_LOCATIONS_ALL we are only taking those data which are relevant for our purpose and our end aim is to view the PO_LINEA_ALL entries corresponding to some required conditions satisfied by entries in PO_LINE_LOCATIONS_AL
Inline view : Subqueries in a from Clause
You can also write subqueries that appear in your from clause. Writing subqueries in the from clause of the main query can be a handy way to collect an intermediate set of data that the main query treats as a table for its own query-access purposes. This subquery in the from clause of your main query is called an inline view. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. The subquery can be a select statement that utilizes joins, the group by clause, or the order by clause
Select a.PO_HEADER_ID, a.Segment1, b.unit_price, b.Quantity
From PO_HEADERS_ALL a,
(
Select unit_price, Quantity, po_header_id
From PO_LINES_ALL
) b
Where a.PO_HEADER_ID=b.PO_HEADER_ID
From PO_HEADERS_ALL a,
(
Select unit_price, Quantity, po_header_id
From PO_LINES_ALL
) b
Where a.PO_HEADER_ID=b.PO_HEADER_ID
