| 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.
Semi-join filters help you narrow your query, such as returning only accounts whose opportunities match a specific criterion.
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.
1query {
2 uiapi {
3 query {
4 Account (where: {
5 Id: { inq: {
6 Opportunity: {
7 StageName: { eq: "Closed Won" } },
8 ApiName:"AccountId"
9 }
10 }
11 }) {
12 edges {
13 node {
14 Id
15 Name { value }
16 }
17 }
18 }
19 }
20 }
21}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.
1SELECT Id, Name
2FROM Account
3WHERE Id IN
4 ( SELECT AccountId
5 FROM Opportunity
6 WHERE StageName = 'Closed Won'
7 )You can also filter the subquery with multiple where conditions like this.
1query {
2 uiapi {
3 query {
4 Account(where: { Id: { inq: { Opportunity: {
5 and: [
6 { StageName: { eq: "Prospecting" } },
7 { Probability: { eq: 20.0 } }
8 ]},
9 ApiName:"AccountId" } } }) {
10 edges {
11 node {
12 Id
13 Name {
14 value
15 displayValue
16 }
17 }
18 }
19 }
20 }
21 }
22}The previous query is similar to this SOQL statement.
1SELECT Id, Name
2FROM Account
3WHERE Id
4IN (
5 SELECT AccountId
6 FROM Opportunity
7 WHERE StageName = 'Prospecting' and
8 Probability = 20.0
9 )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.
1query {
2 uiapi {
3 query {
4 Account(where: {
5 and: [{
6 BillingCountry : {
7 eq: "USA"
8 }}, {
9 Id: { inq: {
10 Contact: {
11 AccountId: { ne: null } },
12 ApiName:"AccountId"
13 }
14 }
15 }]
16 })
17 {
18 edges {
19 node {
20 Id
21 Name {
22 value
23 displayValue
24 }
25 BillingCountry { value }
26 }
27 }
28 }
29 }
30 }
31}The previous query is similar to this SOQL statement.
1SELECT Id, Name, BillingCountry
2FROM Account
3WHERE BillingCountry='USA' AND Id
4IN (
5 SELECT AccountId
6 FROM Contact
7 )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.
1query accounts {
2 uiapi {
3 query {
4 Case (where: {
5 RecordTypeId: { inq: {
6 RecordType: {
7 DeveloperName: { eq: "My_Developer_Name" }}, ApiName:"Id"
8 }
9 }
10 }) {
11 edges {
12 node {
13 Id
14 Subject {
15 value
16 }
17 RecordType {
18 Name {
19 value
20 }
21 }
22 }
23 }
24 }
25 }
26 }
27}The previous query is similar to this SOQL statement.
1SELECT Id, Subject, RecordType.Name
2FROM Case
3WHERE RecordTypeId IN
4( SELECT Id
5 FROM RecordType
6 WHERE DeveloperName = 'My_Developer_Name'
7)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”.
1query {
2 uiapi {
3 query {
4 ContactRequest (where: {
5 WhoId: { inq: {
6 Contact: {
7 FirstName: { like: "R%" } },
8 ApiName:"Id"
9 }
10 }
11 }) {
12 edges {
13 node {
14 Id
15 Who {
16 ... on Contact {
17 Name { value }
18 }
19 }
20 }
21 }
22 }
23 }
24 }
25}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 filters help you narrow your query, such as returning only accounts that don’t match a certain criteria.
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.
1query {
2 uiapi {
3 query {
4 Account (where: {
5 Id: { ninq: {
6 Opportunity: {
7 IsClosed: { eq: false } },
8 ApiName:"AccountId"
9 }
10 }
11 }) {
12 edges {
13 node {
14 Id
15 Name { value }
16 }
17 }
18 }
19 }
20 }
21}The previous query is similar to this SOQL statement.
1SELECT Id
2FROM Account
3WHERE Id NOT IN
4 (
5 SELECT AccountId
6 FROM Opportunity
7 WHERE IsClosed = false
8 )You can also query accounts with associated opportunities that aren’t in prospecting stage.
1query {
2 uiapi {
3 query {
4 Account(where: { Id: { ninq: {
5 Opportunity: { StageName: { eq: "Prospecting" }} , ApiName:"AccountId" } } }) {
6 edges {
7 node {
8 Id
9 Name { value }
10 }
11 }
12 }
13 }
14 }
15}The previous query is similar to this SOQL statement.
1SELECT Id, Name
2FROM Account
3WHERE Id
4NOT IN (
5 SELECT AccountId
6 FROM Opportunity
7 WHERE StageName = 'Prospecting'
8 )This example returns opportunities for all contacts whose source isn’t Web. It shows a child-to-child anti-join from Opportunity to Contact.
1query {
2 uiapi {
3 query {
4 Opportunity (where: {
5 AccountId: { ninq: {
6 Contact: {
7 LeadSource: { eq: "Web" } },
8 ApiName:"AccountId"
9 }
10 }
11 }) {
12 edges {
13 node {
14 Id
15 Name { value }
16 }
17 }
18 }
19 }
20 }
21}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”.
1query {
2 uiapi {
3 query {
4 Account (where: { and: [{
5 Id: { inq: {
6 Contact: { LastName: { like: "G%" } },
7 ApiName:"AccountId" },
8 },
9 Id: { inq: {
10 Opportunity : { IsClosed : { eq : false } },
11 ApiName:"AccountId" }
12 }
13 }] }) {
14 edges {
15 node {
16 Id
17 Name {
18 value
19 }
20 }
21 }
22 }
23 }
24 }
25}The previous query is similar to this SOQL statement.
1SELECT Id, Name
2FROM Account
3WHERE Id IN
4 (
5 SELECT AccountId
6 FROM Contact
7 WHERE LastName LIKE 'G%'
8 )
9 AND Id IN
10 (
11 SELECT AccountId
12 FROM Opportunity
13 WHERE isClosed = false
14 )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.
1query {
2 uiapi {
3 query {
4 Account (where: {
5 Id: { inq: {
6 Contact: { LeadSource: { eq: "Web" } },
7 ApiName:"AccountId" },
8 },
9 }) {
10 edges {
11 node {
12 Id
13 Contacts {
14 edges {
15 node {
16 Id
17 }
18 }
19 }
20 }
21 }
22 }
23 }
24 }
25}The previous query is similar to this SOQL statement.
1SELECT Id, (SELECT Id from Contacts)
2FROM Account
3WHERE Id IN
4 (
5 SELECT AccountId
6 FROM Contact
7 WHERE LeadSource = 'Web'
8 )SOQL and SOSL Reference: Semi-Joins with IN and Anti-Joins with NOT IN
j