Subquery

A subquery is a child query that is nested or embedded within a parent query (also known as the outer query). Subqueries can be used as an alternative to JOINs in a query. Use subqueries when a JOIN field is only needed in the criteria, i.e., in the WHERE clause. The primary reason for using subqueries is to improve performance by savingtime and memory. 
The result of the subqueries will be used to execute the parent query. This approach is especially useful for filtering data based on related records. 
 

Subquery structure

{
"select_query" : "select Lead_Source,Email from Leads where Contacts in (select id from Contacts where Email='patricia@mail.com')"
}

Example: Query without subquery (query using JOIN)

{
 "select_query" : "select Full_Name from Contacts where ((Account_Name.Account_Name='Patricia' ) and (Account_Name.Account_Type='Press'))"
}

Example: Query with subquery

When using a subquery, the Account_Name lookup field is referenced only once.

{
"select_query": "Select Full_Name from Contacts where Account_Name in (select id from Accounts where (Account_Type='Press') and (Account_Name='Patricia'))"
}

Subquery vs JOINs

The below table shows when to use subquery and when to use JOIN.

Scenario

Imagine retrieving the Full Name of contacts whose associated Account Name (lookup inner field) starts with "A".
 

Assume the below records count from the Accounts and Contacts modules:

• In the Accounts module, there are 400 records that start with "A" . 
• In the Contacts module, 
  • There are 1600 records associated with the 400 accounts whose names start with "A". 
  • There are 350 contact records associated to the first 100 accounts out of above 400 accounts, sorted by the ORDER BY clause and the default ID field (default ascending order sorting using the ID field ), where the account name starts with "A". 

Let us see the results when querying with both a JOIN and a Subquery:

Querying with JOIN

{
 "select_query" : "select Full_Name from Contacts where Account_Name.Account_Name like 'A%' limit 2000"
}

The above query will return up to 2000 records of contacts whose Account name starts with "A." Since there are 1600 matching records, this query will retrieve all of them in a single request.

Querying with Subquery

{
"select_query" : "select Full_Name from Contacts where Account_Name in (select id from Accounts where Account_Name like 'A%') LIMIT 2000"
}

The subquery first retrieves the IDs of accounts whose names start with "A" from the Accounts module. Once the account IDs are retrieved, the query searches the Contacts module for all contacts linked to those accounts.

In this case, a single subquery can only retrieve 100 account records at a time. Since there are 400 accounts whose names start with "A", but the subquery fetches only the first 100 accounts, this results in the query retrieving only 350 contact records (assume these 350 contacts are associated with the first 100 accounts).

In this case, if a user knows that there will be more than 100 records returned (as is the case here with 400 account records whose Account Name starts with "A"), it is advisable to use a JOIN rather than a subquery.

Supported Operators

Subquery Support Operators

Multi-value Operators:  Supports multiple values as operands. 

• IN 
• NOT IN

Single-value Operators: Supports a single value as an operand.

• = (equals)
• != (not equals)
• < (less than)
• > (greater than)
• <= (less than or equal to)
• >= (greater than or equal to)
• BETWEEN
• NOT BETWEEN

 Note: When using the BETWEEN and NOT BETWEEN operators with subqueries, ensure each subquery returns a single value. If not, the system will throw an error.

Example:

Sample query with two subquery operands

The below query retrieves the leads whose Created_Time between the first record's creation time and the 201st record's creation time.

{
 "select_query" : "select Last_Name from Leads where Created_Time between (select Created_Time from Leads where id is not null Limit 1) and (select Created_Time from Leads where id is not null limit 200,1)"
}

Sample query with a single subquery operand

The below query retrieves the leads whose Created_Time is between the first record's creation time and the specified date and time range (2024-09-25T23:59:59+05:30).

{
 "select_query" : "select Last_Name from Leads where Created_Time between (select Created_Time from Leads where id is not null Limit 1) and '2024-09-25T23:59:59+05:30'"
}

Subquery Sequence

The subquery sequence is a unique identifier assigned to each subquery based on the order of execution. The execution starts from the 0th index, meaning the first executed subquery gets the sequence "0", the next one "1", and so on.

{
"select_query" : "select id from Contacts where (Account_Name in (select id from Accounts where id is not null) and  Created_Time between (select Created_Time from Accounts where Account_Name like 'A%' Limit 1) and (select Created_Time from Accounts where Account_Name like 'B%' Limit 1)) and  Vendor_Name in (select id from Vendors where id in (select id from Vendors where Vendor_Name like 'V%'))"
}
                        

Execution Process

The outer query is as follows:

select id from Contacts where (Account_Name in (select id from Accounts where id is not null) and  Created_Time between (select Created_Time from Accounts where Account_Name like 'A%' Limit 1) and (select Created_Time from Accounts where Account_Name like 'B%' Limit 1)) and  Vendor_Name in (select id from Vendors where id in (select id from Vendors where Vendor_Name like 'V%'))
        

This outer query relies on several subqueries that must be executed in sequence: 

• The first subquery, (select id from Accounts where id is not null), is independent and is executed first, with a sequence of "0".
• The second subquery, (select Created_Time from Accounts where Account_Name like 'A%' Limit 1), is also independent and executed next, with a sequence of "1".
• The third subquery, (select Created_Time from Accounts where Account_Name like 'B%' Limit 1), is executed next with a sequence of "2".
• The fourth subquery, (select id from Vendors where id in (select id from Vendors where Vendor_Name like 'V%')), depends on the fifth subquery (select id from Vendors where Vendor_Name like 'V%').
  • The fifth subquery executes first, with a sequence of "3".
  • Afterwards, the fourth subquery executes, using the results of the fifth subquery and its sequence of "4".