Overview of Table Joins

Analytics uses inner and outer table joins. The join type that's used to link the tables in your report impacts what records the report can return.

Generally, you do not need to specify which fields are used to join tables together in reports since the tables that are commonly used in reports are often joined automatically. However, some tables can be joined through more than one field and other tables do not have linked fields in the database and cannot directly join together. If you encounter one of these situations when adding a field to a report, you need to specify how the tables are joined. You may also need to change the join type to allow the report to access the appropriate records in the tables.

Note: The relationships between custom object tables and other tables are defined when the custom object is created. See Overview of Custom Objects.

Inner Joins

When tables are linked with an inner join, only records in the tables that share common data can be returned. For example, when the contacts table and the incidents table are linked with an inner join, only contacts that have incidents associated with them can be returned by the report. Information about a contact who has never submitted an incident cannot be returned.

Inner joins are used when tables are automatically joined together since they meet the requirements of most reports and are easier to process in database queries than outer joins.

Outer Joins

When tables are linked with an outer join, all rows from the first table added to the report (referred to as the primary table) can be returned in the report, regardless of their relationship to rows in the secondary table. Information in the secondary table that's related to information in the primary table can also be returned. For example, if accounts is the primary table and incidents is the secondary table in a report, it can return information about all staff accounts, whether or not they have incidents assigned to them. The report can also return information about incidents that are assigned to staff members, but cannot return information about incidents that aren't assigned.

Since outer joins allow all data from the primary table to be returned, but only some from the secondary table, the ordering of the tables is important when using outer joins. If your report does not return the information you want from two tables that are outer joined, you may need to change which table is the primary and which is the secondary. See Change Table Ordering.

Tip: When using outer joins, it can be helpful to add a field from the table you are most interested in first. If your report’s purpose is primarily to output information about contacts, a field from the contacts table should be added as a column before fields from other tables.

Outer joins aren't automatically used in reports since inner joins meet the requirements of most reports and are easier to process in database queries than outer joins. Outer joins are also referred to as left joins by those familiar with database queries.