Join Records from Different DMOs and DLOs

Data resides in various data lake objects (DLOs) and data model objects (DMOs), which this page refers to as DMOs for consistency. To combine customer data stored in separate DMOs, use a join query. A join uses relationships among DMOs based on keys to match a record in one DMO with a corresponding record in another.

For example, the ssot__Individual__dlm DMO contains basic customer information, including first name, last name, and birth date. The customer’s phone number is stored in ssot__ContactPointPhone__dlm, the email in ssot__ContactPointEmail__dlm, and the address in ssot__ContactPointAddress__dlm. To combine this data, use a join query.

Types of Joins

Inner join: returns only records with matching field values from both DMOs.
Full outer join: returns all records from both DMOs. Missing values are NULL.
Left outer join: returns all records from the left DMO and matching records from the right DMO. Missing values are NULL.
Right outer join: returns all records from the right DMO and matching records from the left DMO. Missing values are NULL.

Get Matching Records with an Inner Join

To retrieve customer email addresses, use an inner join between ssot__Individual__dlm and ssot__ContactPointEmail__dlm. This retrieves emails by matching Individual records with records in ssot__ContactPointEmail__dlm based on ssot__Id__c and KQ_Id__c. The KQ_Id__c is the fully qualified key that ensures the query can accurately identify the difference between the keys.

1SELECT
2    i."ssot__Id__c",
3    i."ssot__FirstName__c" AS FirstName,
4    i."ssot__LastName__c" AS LastName,
5    c."ssot__EmailAddress__c" AS Email
6FROM "ssot__Individual__dlm" AS i
7INNER JOIN "ssot__ContactPointEmail__dlm" AS c
8ON i."ssot__Id__c" = c."ssot__PartyId__c" AND
9   i."KQ_Id__c" IS NOT DISTINCT FROM c."KQ_PartyId__c"

This table displays 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`

Records with NULL key fields don’t match when you join using =. Use IS NOT DISTINCT FROM instead. In the example, KQ_Id__c uses IS NOT DISTINCT FROM because it can be NULL. For more information about fully qualified keys, see Salesforce Help: Fully Qualified Keys.

Note

This query joins UnifiedIndividual__dlm and UnifiedContactPointEmail__dlm. Unified DMOs lack KQ_Id__c because there are no duplicate record IDs.

1SELECT
2    i."ssot__Id__c",
3    i."ssot__FirstName__c" AS FirstName,
4    i."ssot__LastName__c" AS LastName,
5    c."ssot__EmailAddress__c" AS Email
6FROM "UnifiedIndividual__dlm" AS i
7INNER JOIN "UnifiedContactPointEmail__dlm" AS c
8ON i."ssot__Id__c" = c."ssot__PartyId__c"

The query uses aliases. The AS keyword is optional.

This example joins the Individual DMO with multiple Commerce DMOs (such as the SalesOrder DMO).

1SELECT
2    i."ssot__FirstName__c" AS first_name,
3    i."ssot__LastName__c" AS last_name,
4    p."ssot__Name__c" AS product_name,
5    p."ssot__PrimaryProductCategory__c" AS category_name,
6    so.*
7FROM
8    "ssot__Individual__dlm" i
9JOIN
10    "ssot__SalesOrder__dlm" so ON so."ssot__SoldToCustomerId__c" = i."ssot__Id__c"
11JOIN
12    "ssot__SalesOrderProduct__dlm" sop ON so."ssot__Id__c" = sop."ssot__SalesOrderId__c"
13JOIN
14    "ssot__GoodsProduct__dlm" p ON sop."ssot__ProductId__c" = p."ssot__Id__c"
15WHERE
16    so."ssot__SalesOrderStatusId__c" = 'Complete'

Get Matching and Non-Matching Records with Outer Joins

To retrieve all records, including non-matching ones, use a full outer join. NULL values indicate missing matches.

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.

1SELECT
2    i."ssot__Id__c",
3    i."ssot__FirstName__c" AS FirstName,
4    i."ssot__LastName__c" AS LastName,
5    c."ssot__EmailAddress__c" AS Email
6FROM "ssot__Individual__dlm" AS i
7FULL OUTER JOIN "ssot__ContactPointEmail__dlm" AS c
8ON i."ssot__Id__c" = c."ssot__PartyId__c" AND
9   i."KQ_Id__c" IS NOT DISTINCT FROM c."KQ_PartyId__c"

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`