More subqueries - speaker notes:
This slide show continues the topic of subqueries.
Pairwise means that the inner select returned to pairs (actually three but the second and the third are the same).
The outer query no finds all records that match these two pairs.
The pairwise subquery uses the inner select to retrieve values for a pair or multiple fields/columns. These values are then used to retrieve all records that match the retrieved values. In this example, the inner select retrieved code1 and code2 according to specific criteria.
The outer select then find all records that matched this pair.
In this pairwise subquery, the match is based on all three codes.
The inner select retrieves the three codes for the records/rows that match the WHERE criteria.
The outer select retrieves all records/rows that match the pairs of three codes that were retrieved in the inner select.
The criteria is that the dept be IN the group of departments retrieved from the first inner select and that itemclass be IN the group of itemclasses retrieved from the second inner select. They do not have to be in pairs.
The only record not selected is 1111 Good Night Moon which has a BY in itemclass and therefore does not meet the criteria of the second inner select.
Non pairwise means that code1 has to appear in the results of the first inner query and code2 has to appear in the results of the second inner query, but they do not have to appear as a pair.
This is similar to the non pairwise subquery on the previous slide. This time a similar SELECT is non using as a pairwise subquery.
This slide has a where clause on the outer select so the records that the outer select displays have to meet two criteria. Code3 cannot be CD and code1 and code2 have to match the pairs of code1 and code2 retrieved by the inner select.
Note that the inner select eliminated records with code3! = CD, however that only affects the information retrieved by the inner select. The outer select works with what it is given and is only concerned with matching to the XX BB.
The problem with null values in a subquery is that if one of the values returned when the inner query is executed is null, then no rows are returned. In this case code2 is null therefore one of the value returned is null and no rows are selected.
The null value problem only applies if the results of the inner select include the null value. In this case they do not so the processing is successful.
Word of caution - be careful with NULL and be careful with NOT when construction subqueries.
The NVL can temporarily change CHAR and VARCHAR2 columns/fields to spaces and number fields to 0 or another default value to avoid problems with NULL.