# Semi-Join and Anti-Join Filters

| AVAILABLE API VERSION |
| --------------------- |
| API v56.0 and later   |

A semi-join is a subquery on another object in an `in` clause that restricts the records returned by the primary query. Conversely, an anti-join is a subquery on another object in a `nin` clause that restricts the records returned.

The subquery can filter by ID or a [reference field](https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/field_types.htm).

- If you filter by an ID field, you can create parent-to-child semi-joins and anti-join, such as Account to Contact or Account to Opportunity.
- If you filter by a reference field, you can create child-to-child semi- or anti-joins, such as Contact to Opportunity. You can also create child-to-parent semi- or anti-joins, such as Opportunity to Account.

## Semi-Join Filter

Semi-join filters help you narrow your query, such as returning only accounts whose opportunities match a specific criterion.

### Semi-Join with ID Field

You can include a semi-join in a `where` argument. With the `inq` filter type for the ID field, you can restrict the records returned by the primary query.

This example returns accounts if an associated opportunity is closed won.

```sfdocs-code {"lang":"graphql", "title": "Query accounts with a semi-join on opportunities", "src": "" }
query {
  uiapi {
    query {
      Account (where: {
          Id: { inq: {
            Opportunity: {
              StageName: { eq: "Closed Won" } },
              ApiName:"AccountId"
              }
            }
        }) {
            edges {
              node {
                Id
                Name { value }
              }
            }
        }
     }
  }
}
```

This example is a parent-to-child semi-join from Account to Opportunity, which uses Id on the Account and AccountId on the opportunity.

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"soql", "title": "SOQL statement with IN Clause", "src": "" }
SELECT Id, Name
FROM Account
WHERE Id IN
  ( SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Won'
  )
```

You can also filter the subquery with multiple `where` conditions like this.

```sfdocs-code {"lang":"graphql", "title": "Find accounts whose opportunities are in prospecting stage at 20% probability", "src": "" }
query {
  uiapi {
    query {
      Account(where: { Id: { inq: { Opportunity: {
        and: [
          { StageName: { eq: "Prospecting" } },
          { Probability: { eq: 20.0 } }
      ]},
          ApiName:"AccountId" } } }) {
            edges {
              node {
                Id
                Name {
                  value
                  displayValue
                }
              }
            }
        }
     }
  }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with the IN clause", "src": "" }
SELECT Id, Name
FROM Account
WHERE Id
IN (
    SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Prospecting' and
    Probability = 20.0
    )
```

To restrict the records to show only those records with associated child records, use empty curly brackets `{}` or filter the Id on the subquery with `ne: null`.

```sfdocs-code {"lang":"graphql", "title": "Find accounts with billing in USA with associated contacts", "src": "" }
query {
  uiapi {
    query {
      Account(where: {
        and: [{
          BillingCountry : {
          eq: "USA"
        }}, {
          Id: { inq: {
            Contact: {
              AccountId: { ne: null } },
              ApiName:"AccountId"
          }
              }
        }]
      })
      {
            edges {
              node {
                Id
                Name {
                  value
                  displayValue
                }
                BillingCountry { value }
              }
            }
        }
     }
  }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with WHERE and IN clauses", "src": "" }
SELECT Id, Name, BillingCountry
FROM Account
WHERE BillingCountry='USA' AND Id
IN (
    SELECT AccountId
    FROM Contact
    )
```

### Semi-Join with RecordTypeId Field

To filter records based on a record type, pass the `RecordTypeId` field to the `inq` filter.

This example returns cases that match a specified `DeveloperName` value on the `RecordType` field.

```sfdocs-code {"lang":"graphql", "title": "Find cases with a specific record type name", "src": "" }
query accounts {
    uiapi {
         query {
            Case (where: {
              RecordTypeId: { inq: {
                RecordType: {
                  DeveloperName: { eq: "My_Developer_Name" }}, ApiName:"Id"
                }
              }
        }) {
                 edges {
                     node {
                         Id
                          Subject {
                             value
                         }
                       RecordType {
                         Name {
                           value
                         }
                       }
                     }
                 }
             }
         }
    }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with WHERE and IN clauses", "src": "" }
SELECT Id, Subject, RecordType.Name
FROM Case
WHERE RecordTypeId IN
( SELECT Id
  FROM RecordType
  WHERE DeveloperName = 'My_Developer_Name'
)
```

### Semi-Join with a Reference Field

A reference field contains an Id value that points to a record on another object. The name of a reference field ends with `Id`, such as `AccountId` or `CaseId`. For example, the `AccountId` field on a contact identifies a unique account record.

Some objects have `WhoId` or `WhatId` reference fields, which can point to one of several other objects. The `WhoId` field can point to a contact or lead. See [Reference Field Type](https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/field_types.htm).

This example returns contact requests for all contacts whose first name starts with "R".

```sfdocs-code {"lang":"graphql", "title": "Query ContactRequest with WhoId field", "src": "" }
query {
  uiapi {
    query {
      ContactRequest (where: {
          WhoId: { inq: {
            Contact: {
              FirstName: { like: "R%" } },
              ApiName:"Id"
          }
              }
      }) {
            edges {
              node {
                Id
                Who {
                  ... on Contact {
                    Name { value }
                  }
                }
              }
            }
        }
     }
  }
}
```

The `WhoId` field in the semi-join is a [polymorphic reference field](/docs/platform/graphql/guide/filter-polymorphic.md) because it can point to a contact or a lead. In this case, we restrict the subquery to contact records.

## Anti-Join Filter

Anti-join filters help you narrow your query, such as returning only accounts that don’t match a certain criteria.

### Anti-Join with ID Field

You can include an anti-join in a `where` argument. With the `ninq` filter type for the ID field, you can restrict the records returned by the primary query.

This example returns accounts that don’t have any open opportunities.

```sfdocs-code {"lang":"graphql", "title": "Query accounts with an anti-join on opportunities", "src": "" }
query {
  uiapi {
    query {
      Account (where: {
          Id: { ninq: {
            Opportunity: {
              IsClosed: { eq: false } },
              ApiName:"AccountId"
          }
              }
      }) {
            edges {
              node {
                Id
                Name { value }
              }
            }
        }
     }
  }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"soql", "title": "SOQL statement with the NOT IN clause", "src": "" }
SELECT Id
FROM Account
WHERE Id NOT IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE IsClosed = false
  )
```

You can also query accounts with associated opportunities that aren’t in prospecting stage.

```sfdocs-code {"lang":"graphql", "title": "Find accounts with an anti-join on opportunities", "src": "" }
query {
  uiapi {
    query {
      Account(where: { Id: { ninq: {
        Opportunity: { StageName: { eq: "Prospecting" }} , ApiName:"AccountId" } } }) {
          edges {
            node {
              Id
              Name { value }
            }
          }
        }
      }
   }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with NOT IN clause", "src": "" }
SELECT Id, Name
FROM Account
WHERE Id
NOT IN (
    SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Prospecting'
    )
```

### Anti-Join with a Reference Field

This example returns opportunities for all contacts whose source isn’t Web. It shows a child-to-child anti-join from Opportunity to Contact.

```sfdocs-code {"lang":"graphql", "title": "Query opportunities with AccountId field", "src": "" }
query {
  uiapi {
    query {
      Opportunity (where: {
        AccountId: { ninq: {
          Contact: {
            LeadSource: { eq: "Web" } },
            ApiName:"AccountId"
          }
        }
      }) {
          edges {
            node {
              Id
              Name { value }
            }
          }
        }
    }
  }
}

```

## Multiple Semi-Joins or Anti-Joins

You can combine semi-join or anti-join clauses in a query. This example returns accounts that have open opportunities if the last name of the associated account starts with "G".

```sfdocs-code {"lang":"graphql", "title": "Query accounts with multiple semi-joins on contacts and opportunities", "src": "" }
query {
  uiapi {
    query {
      Account (where: { and: [{
        Id: { inq: {
          Contact: { LastName: { like: "G%" } },
          ApiName:"AccountId" },
         },
         Id: { inq: {
           Opportunity : { IsClosed : { eq : false } },
           ApiName:"AccountId" }
         }
       }] }) {
            edges {
              node {
                Id
                Name {
                  value
                }
              }
            }
        }
     }
  }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with multiple semi-joins", "src": "" }
SELECT Id, Name
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'G%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )
```

## Evaluate Relationship Queries with Semi-Joins or Anti-Joins

You can create a semi-join or anti-join that evaluates a relationship query. This type of query returns object of one type based on criteria that applies to objects of another type.

This example returns accounts and their associated contacts if the contact lead source is Web.

```sfdocs-code {"lang":"graphql", "title": "Query accounts with a semi-join on contacts", "src": "" }
query {
  uiapi {
    query {
      Account (where: {
        Id: { inq: {
          Contact: { LeadSource: { eq: "Web" } },
          ApiName:"AccountId" },
         },
       }) {
            edges {
              node {
                Id
                Contacts {
                  edges {
                    node {
                      Id
                    }
                  }
                }
              }
            }
        }
     }
  }
}
```

The previous query is similar to this SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with", "src": "" }
SELECT Id, (SELECT Id from Contacts)
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LeadSource = 'Web'
  )
```

## See Also

[SOQL and SOSL Reference: Semi-Joins with IN and Anti-Joins with NOT IN](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm)