| 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.
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
This example returns contact requests for all contacts whose first name starts with "R".
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 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.
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.
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.
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.
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.
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".
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.
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.
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.
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