Join Records from Different Objects to Get a Rich Result Set

Data resides in various DLOs and DMOs, which we refer to here as tables for simplicity. For example, the ssot__Individual__dlm DMO contains basic information about a customer, including the first name, last name, and birth date. The customer's phone number is stored in a separate DMO, ssot__ContactPointPhone__dlm. Similarly, ssot__ContactPointEmail__dlm contains the customer's email and the customer's address is in ssot__ContactPointAddress__dlm. To combine customer data that's stored in separate tables, perform a join query. A join uses relationships between tables based on keys to match a record in one table with a corresponding record in another table.

The different types of join queries

An inner join joins records from two tables based on a common field that exists in the DMOs. It returns only the records from the specified tables that have matching field values.
A full outer join returns a comprehensive data result set so that you can inspect the records that match and the ones that don’t. The missing field values in records that don’t have a match are null.
A left outer join joins records from two tables based on a common field that exists in both tables. It returns all the records from the left table, including unmatched records, and only the matching records from the right table. The missing field values in records that don’t have a match are null.
A right outer join joins records from two tables based on a common field that exists in both tables. It returns all the records from the right table, including unmatched records, and only the matching records from the left table. The missing field values in records that don’t have a match are null.

Let's say you want to retrieve the customer’s phone number. To do so, you need to write a query that performs an inner join between the ssot__Individual__dlm DMO and the ssot__ContactPointPhone__dlm DMO. It gets the phone number of all individuals in ssot__Individual__dlm by matching the Individual records with the records in ssot__ContactPointPhone__dlm based on the Individual ID value and the key qualifier (KQ_Id__c), which are in both DMOs.

This output shows sample query results.

`ssot__Id__c`	`FirstName`	`LastName`	`Email`
`00QHu00003W7JIDMA3`	`Patricia`	`Feager`	`patricia_feager@is.com`
`00QHu00003W7JIRMA3`	`Bill`	`Dadio Jr`	`bill_dadio@zenith.com`
`003Hu00003SELnNIAX`	`Edna`	`Frank`	`efrank@genepoint.com`

If key fields that are null are joined by using =, records don’t match on those fields. To match null field values, use IS NOT DISTINCT FROM instead of = in the ON statement of the join. In the query example, because the fully qualified keys can be null, the condition for the key qualifier uses IS NOT DISTINCT FROM.

This query is an alternative to the previous one but is on the unified data. It performs a join between UnifiedIndividual__dlm and UnifiedContactPointEmail__dlm. The unified DMOs don’t include a fully qualified key field, KQ_Id__c, like the non-unified DMOs because there are no duplicate record IDs.

The query uses aliases for the table names and some field names. The AS keyword is optional. For example, i is an alias for ssot__Individual__dlm: "ssot__Individual__dlm" AS i

And FirstName is an alias for the ssot__FirstName__c field: i."ssot__FirstName__c" AS "FirstName"

This example shows a query that performs multiple joins between the Individual DMO and multiple Commerce DMOs. You can add the Commerce DMOs, such as the SalesOrder DMO, in your org by using the Commerce data bundle. The query joins customer, sales order, and product DMOs to show all completed orders. It shows all fields of SalesOrder, the customer full name, and the product name and product category. The keyword JOIN is equivalent to INNER JOIN.

If you want to get all records in a join query, including the ones that don’t have a match between two tables, use a full outer join. The missing field values in the returned records are null.

This query is similar to the first query for the inner join but it performs an outer join by using the FULL OUTER JOIN keyword. It joins the ssot__ContactPointEmail__dlm DMO with the ssot__ContactPointEmail__dlm DMO based on the Individual ID value and the fully qualified key (KQ_Id__c), which are in both DMOs. In addition to the matching records, it also returns the non-matching records from each DMO.

This output shows sample query results, which include matching and non-matching records. The row for Janet Lorre has no email value because this record has no corresponding entry in ssot__ContactPointEmail__dlm.

`ssot\_\_Id\_\_c`	`FirstName`	`LastName`	`Email`
`003Hu00003SELnCIAX`	`John`	`Bond`	`bond_john@grandhotels.com`
`003Hu00003SELnPIAX`	`Janet`	`Lorre`
`003Hu00003SELnLIAX`	`Tom`	`Ripley`	`tripley@uog.com`

Also, you can perform these other types of outer joins.

Left outer join: Retrieves all records from the left table, including non-matching ones, and only the matching records from the right table. Use LEFT OUTER JOIN in the query instead of FULL OUTER JOIN.
Right outer join: Retrieves all records from the right table, including non-matching ones, and only the matching records from the left table. Use RIGHT OUTER JOIN in the query instead of FULL OUTER JOIN.