---
schema_version: "1"
id: 2b3f41dd7c1f9b90c4f7c3ba4d95e5c78bfabfadeb18184553dd228fa1d5dca0
title: Join Records from Different DMOs and DLOs
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/guides/get-started-dc-sql/query-joins.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/guide/query-joins.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 60782945e037c59f0011ace2e633bb6ee4420fb91ddb4245b91c5adafd10c8c3
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# 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.

```sql
SELECT
    i."ssot__Id__c",
    i."ssot__FirstName__c" AS FirstName,
    i."ssot__LastName__c" AS LastName,
    c."ssot__EmailAddress__c" AS Email
FROM "ssot__Individual__dlm" AS i
INNER JOIN "ssot__ContactPointEmail__dlm" AS c
ON i."ssot__Id__c" = c."ssot__PartyId__c" AND
   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`   |

**note:** 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](https://help.salesforce.com/s/articleView?id=data.c360_a_fully_qualified_keys.htm&language=en_US&type=5).

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

```sql
SELECT
    i."ssot__Id__c",
    i."ssot__FirstName__c" AS FirstName,
    i."ssot__LastName__c" AS LastName,
    c."ssot__EmailAddress__c" AS Email
FROM "UnifiedIndividual__dlm" AS i
INNER JOIN "UnifiedContactPointEmail__dlm" AS c
ON 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_](https://developer.salesforce.com/docs/atlas.en-us.c360a_api.meta/c360a_api/c360dm_sales_order_dmo.htm)).

```sql
SELECT
    i."ssot__FirstName__c" AS first_name,
    i."ssot__LastName__c" AS last_name,
    p."ssot__Name__c" AS product_name,
    p."ssot__PrimaryProductCategory__c" AS category_name,
    so.*
FROM
    "ssot__Individual__dlm" i
JOIN
    "ssot__SalesOrder__dlm" so ON so."ssot__SoldToCustomerId__c" = i."ssot__Id__c"
JOIN
    "ssot__SalesOrderProduct__dlm" sop ON so."ssot__Id__c" = sop."ssot__SalesOrderId__c"
JOIN
    "ssot__GoodsProduct__dlm" p ON sop."ssot__ProductId__c" = p."ssot__Id__c"
WHERE
    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.

```sql
SELECT
    i."ssot__Id__c",
    i."ssot__FirstName__c" AS FirstName,
    i."ssot__LastName__c" AS LastName,
    c."ssot__EmailAddress__c" AS Email
FROM "ssot__Individual__dlm" AS i
FULL OUTER JOIN "ssot__ContactPointEmail__dlm" AS c
ON i."ssot__Id__c" = c."ssot__PartyId__c" AND
   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`           |