Oracle Database 12c: joining and outer joining with collections

Joining is a key ingredient in most SQL queries. Using collections (aka nested tables) that are produced on the fly inside the SQL query or that are returned by a PL/SQL function that is invoked from a TABLE operator in the query is a powerful weapon as well. Oracle Database 12c has extended the SQL syntax and functionality for joining with collections in such a way that the call to the PL/SQL function that creates the collection can take an input parameter taken from the records from the table to which it is joined. That is a little abstract, so let’s quickly look at an example:

Here we perform a join between table DEPT and a PL/SQL function that returns a collection. This function takes an input parameter – a department number – and returns in the collection the names of all employees in that department. As part of the join, the set of records that the join is performed with for a certain record in the base set (table DEPT in this case) is established. When the function returns an empty collection for a certain record in table DEPT, no join is performed for that (DEPT) record and that record is removed from the result set. (this happens in this case to Department 40).

The PL/SQL function employees_in_department that is invoked in this case is defined as follows:

This function can be invoked on its own to get a clear impression of what it does:

Now we have seen how the CROSS APPLY will help us join with the collection result from function that takes a join-value as its input. However, in the process, we have lost department 40 (because it does not have any employees and results in an empty collection in the employees_in_department function).

Next to CROSS APPLY we can also use OUTER APPLY. It does the same thing, except that when a record from the source of the join does not result in a matching collection with one or more records, it will still be included in the result of the join. Just like the normal outer join that you surely know (LEFT OUTER JOIN and RIGHT OUTER JOIN).

The result of this query will include Department 40 – without a joining employee name:

Some formal rules for APPLY:

• the APPLY keyword allows you to specify a table_reference or collection_expression on the right side of a join clause.
• The table_reference can be a table, inline view, or TABLE collection expression; it cannot be a lateral inline view.
• The collection_expression can be a subquery, a column, a function, or a collection constructor.
• Regardless of its form, it must return a collection value—that is a nested table or varray.
• Left correlations are allowed on the right side of APPLY.
• With keyword CROSS – only rows from table that are joined with result in right side are returned
• With keyword OUTER – both rows that join and those that do not are returned from the table (on the left)

Resources

Download Oracle Database 12c at eDelivery or from OTN: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

Oracle Database 12c Documentation: http://www.oracle.com/pls/db121/homepage. Specific section on CROSS APPLY and OUTER APPLY: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#BABJHDDA.