Skip Navigation
Expand
Types of table joins used with reports
Answer ID 1839   |   Last Review Date 07/22/2019

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 simplified incidents and accounts tables listed below will be used to better illustrate the differences between inner and outer joins.
Ref_no Incident Status Incident Assigned Accounts Acct_ID Accounts Display Name
001 Unresolved 32 4 Jim
002 Solved 15 9 Paula
003 Unresolved 15 15 Brenda
004 Unresolved (null) 27 Tom
005 Updated 27 32 Ken

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.

Table Instances: incidents inner join to accounts

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.

If the two simplified tables above are joined using an inner join, the resulting dataset is:
Ref_no Status Assigned/
Acct_ID
Display Name
001 Unresolved 32 Ken
002 Solved 15 Brenda
003 Unresolved 15 Brenda
005 Updated 27 Tom

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.

Table Instances: incidents outer join to accounts

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

...the resulting dataset is:
Ref_no Status Assigned/
Acct_ID
Display Name
001 Unresolved 32 Ken
002 Solved 15 Brenda
003 Unresolved 15 Brenda
004 Unresolved (null) (null)
005 Updated 27 Tom

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.

Table Instances: accounts outer join to incidents

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

... the resulting dataset would be:
Display Name Acct_ID/
Assigned
Ref_no Status
Jim 4 (null) (null)
Paula 9 (null) (null)
Brenda 15 002 Solved
Brenda 15 003 Unresolved
Tom 27 005 Updated
Ken 32 001 Unresolved

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 online documentation for the version your site is currently running. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.

Available Languages for this Answer:

Notify Me
The page will refresh upon submission. Any pending input will be lost.