---
schema_version: "1"
id: 1e4030b4fc0539bd88d56e2dec27db55e4fee846743fbdb850698e454cad664b
title: Optimize Data Operations with Semi and Anti Joins
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_explorer_data_join.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_explorer_data_join.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: d483693b69f52915db33a801d37dffd5e84bed81e92047ec3a275685f83484f2
description: Semi- and anti-joins offer a straightforward way to compare datasets in a lens. They're particularly useful for identifying the presence or absence of related rows without needing complex queries. For example, you can easily find accounts that have at least one opportunity or those that have no opportunities.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# Optimize Data Operations with Semi and Anti Joins

Semi- and anti-joins offer a straightforward way to compare datasets in a lens. They're particularly useful for identifying the presence or absence of related rows without needing complex queries. For example, you can easily find accounts that have at least one opportunity or those that have no opportunities.

1. Open a lens for the primary dataset.
2. Click **Join Data Source**.
    
    ![Shows the menu to add a dataset.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_explorer_data_join_3.png)
3. Select a secondary dataset on the next screen.
4. Select a join type:
    
    The lens defaults to a semi-join.
    
    1. **Semi-join** returns the rows from the primary dataset that have a match in the second dataset, without returning any columns from the second dataset. This is useful for filtering data based on the existence of related records.
    2. **Anti-join** returns only the rows from the primary dataset that don't have a match in the second dataset. It's useful for finding records that don't have any related entries.
    3. Select fields to match between the datasets. Click **Add Fields** to add more fields.
    4. Click **Done**.
    
    ![Screen showing join types, join definition, and join fields.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_explorer_data_join.png)
5. To change the join type:
    
    1. Click **Edit** in the Datasets area.
    2. Select the desired join type and fields.
    3. Click **Done**.
    
    ![Link to change join type.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_explorer_data_join_2.png)
6. To remove a secondary dataset:
    
    1. Click the down arrow across from the dataset, and select **Remove Dataset**.
    2. To join with a different dataset, repeat steps 2–4 .
    
    ![Selecting Remove Dataset.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_explorer_data_join_5.png)
7. To create a meaningful analysis, select measures and fields to group by in the dataset result.
    
    When defining groups and measures, fields are selected from the primary dataset.
    
    ![Shows how to add measures and groups.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_explorer_data_join_4.png)

Unsupported Operations:

- cogroup and joins can't be used in the same query.
- Totals and subtotals arent supported.
- Boolean filter logic isn't supported.
- Blends and joins can't be combined in the same query.

Optimizing Performance:

- Filter the second dataset before running a join. Join performance is directly proportional to the amount of data returned by the second dataset.
- Execute join statements before any projections on the query results. For example, if your query includes a `foreach` statement such as, `q = foreach q generate count(q1) as 'A';`, run it after the join.

Supported Filters

Filters can be applied to both the primary and secondary datasets.

Join Limitations

| Limitation | Details |
| --- | --- |
| Dataset Limit | You can join a maximum of two datasets. For combining more datasets (iup to six), consider using a blend. |
| Field Pairings | Up to five field pairings are allowed between datasets. |
| Order of Operations | Datasets must be joined before exploration. Join Data Source becomes unavailable if groupings, measures, or filters are added first. |
| Self-Joins | Combining rows within the same dataset is supported. When a filter is appled in a self-join, only one dataset is visible. Global filters are applied to the primary dataset in the join. |
| Primary Dataset Focus | Faceting and record-level actions are applied exclusively to the prmary dataset. |

- **[Limitations and Considerations for Semi and Anti Joins](https://help.salesforce.com/s/articleView?id=analytics.bi_explorer_data_join_considerations.htm&release=264.0.0&type=5)**  
    Semi and anti joins are powerful tools for filtering data based on the existence of related records. To ensure accurate results and optimal performance, consider these specific behaviors when getting started.