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