---
schema_version: "1"
id: 028146984dec6b65fdfed387a340b45e29fba2cc573d2593192d72778847a1e6
title: Considerations When Using Joins
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_data_prep_recipe_transformation_joinconsiderations.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_data_prep_recipe_transformation_joinconsiderations.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: a6819eab16ea39d96ff2d385d442d7d3f4f9de52e2e6f9ac9351aa225e27ced0
description: In CRM Analytics, unlike a lookup, a join creates a separate record for each match in the target dataset when multiple rows match. Before using a join, ensure that you understand the implications of duplicate rows.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# Considerations When Using Joins

In CRM Analytics, unlike a lookup, a join creates a separate record for each match in the target dataset when multiple rows match. Before using a join, ensure that you understand the implications of duplicate rows.

### Required Editions

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small edition"><colgroup><col></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in Salesforce Classic and Lightning Experience.</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available with CRM Analytics, which is available for an extra cost in <strong>Enterprise</strong>, <strong>Performance</strong>, and <strong>Unlimited</strong> Editions. Also available in <strong>Developer</strong> Edition.</td></tr></tbody></table>

Review the following tips.

Don’t double count measures when aggregating records from a join.

Consider the following two data streams that feed the recipe’s target dataset. Both input data streams have duplicate key values.

![The left data stream shows the opportunity ID, customer name, and opportunity amount. The right data stream shows the opportunity ID and educational level.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_join_doubleCountIssue_dataStreams.png)

A left join duplicates the Opp\_ID 1 record in the left data stream because it has multiple matches in the right data stream.

![The new dataset contains columns from both data streams.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_join_doubleCountIssue_results.png)

Notice that the duplicated records repeat the opportunity amount for Alice. If you added all opportunity amounts to get the total, double count the amount for Alice. To prevent duplicate records, use a lookup instead of a join.

Refrain from using joins when the join keys have a many-to-many relationship.

When the join keys have a many-to-many relationship, the target dataset can become significantly larger than the input data streams. For instance, if four records on the left and five records on the right have the same key value, the join adds 20 (4\*5) records to the target dataset. In a more extreme case, if 10,000 records on the left and 5,000 on the right share a key value, the join creates 50 million records in the target dataset.

To illustrate why this occurs, consider the following two data streams that feed the recipe’s target dataset. Both input data streams have duplicate key values.

![The left data stream shows the opportunity ID, customer name, and opportunity amount. The right data stream shows the opportunity ID and educational level.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_join_dataExplosionIssue_dataStreams.png)

A left join duplicates each record in the left data stream five times because it has five matches in the right data stream.

![The new dataset contains columns from both data streams.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_join_dataExplosionIssue_results.png)

To prevent duplicate records, use a lookup instead of a join. If you must use a join, try adding more key fields to make the keys have more unique values.