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)
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.
- Weird ADF 11g requirement addressed with left outer join and modern SQL join syntax
- Summary results for all dates, including the ones that I do not have data for – example of using Partition Outer Join – Oracle10g SQL Feature
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Tag Cloud analysis in SQL and PL/SQL – Using Collections, Table Operator, Multiset and Collect operator
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- WebLogic & FMW Provisioning update
- The road ahead for WebLogic 12c
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- OOW13: summarizing one week and 2000 sessions in 3 hours and a bit – the yearly AMIS OOW Review session – 10th October
- OOW13 and JavaOne 13: Notes from a Conference – Part Two
- OOW13: First glimpses of the new SOA Suite 12c