When I add more than one table to a report, I have options to Toggle Join Type. What are the join types? What are table joins?

Environment:

Analytics
Oracle B2C Service, All versions

Resolution:

Table joins allow you to link data from two or more database tables into a single query result. This allows you to view and display data from multiple tables in a single report. When you create a report that uses data from multiple tables, you can specify which type of join to use between the tables.

To successfully join information from two tables, each table must contain a data field that is common to both tables. The fields do not need to have the same name within each table, but MUST refer to a common element in both tables.

Determining Join Types Between Tables

When tables are joined in a report, the icon used illustrates the join type. If the circle on the left is entirely filled in as blue, there is an outer join between the tables. If only the overlapping section of the icon is blue, the join between the tables is an inner join.

To determine how tables are joined in a report, use the steps below:

1. From the Reports Explorer, open the report for editing. Or if the report is listed in your navigation items, right click on the report and select Edit Report Definition.
      
2. Select Tables from the ribbon.  Click the indented table listed and review the Join Type.
   
   
3. To change the join type, click the Outer or Inner radio button and click OK. Then, save the report.

Simplified Examples

For example, in the Oracle B2C Service database, the incidents table includes the assigned field which specifies which staff member the incident is assigned to by the account ID. The accounts table lists all staff accounts and includes the acct_id field. Since the incidents.assigned field and the accounts.acct_id field both refer to the same information (a staff account ID), the incidents and accounts tables can be joined together. As a result, joining these tables allows you to run reports or use views that include data from both tables, such as the number of incidents assigned to each staff member.

The simplified incidents and accounts tables listed below will be used to better illustrate the differences between inner and outer joins.

The specific data that is included in the query result depends on whether an inner or outer join is used in the query. If you select Toggle Join Type when working with the table instances in a view or report, the type of join will change from an inner join to an outer join or from an outer join to an inner join. To change the type of join between the tables, right click on the lower of the two tables and select Toggle Join Type.

The icon of overlapping circle indicates which type of join is used between the tables. If only the intersection of the circles is shaded, the join is an inner join. If the entire left circle is shaded, the join is an outer join. To see more specific information about how the tables are joined, right click on the table name and select Node Info.

Inner Joins

An inner join returns data from the query ONLY if the value in the field in the first table also appears as a value in the field of the second table. This means that data must exist in both fields in both tables in order for the data to be returned in the query.

With inner joins, the ordering of the tables has no effect on the resulting data set.

Example: Using the case of the incidents and accounts tables, if the inner join is used, the query result will include ONLY the incidents that are assigned to a staff member. Incidents that are unassigned do not have a value in the accounts table, which means those incidents are not included in the result set. Similarly, staff members that do not have incidents assigned to them will not appear in the query results since they do not have incidents.

Note: With the inner join, incident 004 does not appear in the query results (because it is not assigned to a staff member) and Jim and Paula are not listed in the dataset because they do not have incidents assigned to them.

Outer Joins

An outer join is also referred to as a left join. With an outer join, the query results include all of the data from the first table, and where the data exists in the second table, data from the second table is included as well for each record. With an outer join, it is possible that the row of data returned includes only the information from the first (or left) table. Null values can exist for the fields associated with the second table.

When working with outer joins, the ordering of the tables is very important. The resulting dataset is quite dependent on which table is listed first for the join.

Example: Using the simplified incidents and accounts tables, an outer join with the incidents table as the first table...

Note: With this outer join, incident 004 is included in the results. Paula and Jim do not appear in the results since they cannot be associated with any of the incidents.

Example: Using the simplified incidents and accounts tables, with the accounts table as the first table...

Note: With this outer join, Jim and Paula ARE listed in the dataset, even though they do not have incidents assigned to them. Incident 004 is not included in the results because it cannot be associated with a staff member.

For additional information, refer to the 'Types of Table Joins' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.