JOINs in COQL Queries
The SQL JOIN clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
In COQL, you can establish a JOIN or a relation with the help of lookup fields that relate one module with the other using the .(dot) operator.
Consider that you want to establish a JOIN between two tables - Contacts and Accounts.
In Contacts, Account_Name is a lookup field to the Accounts module. 
In Accounts, the Parent_Account field is another lookup to Accounts itself, that represents the parent account of an account. 
Consider that you want to fetch records from Contacts, where the parent account of the account associated with the contact is "King".
COQL Query
{
"select_query": "select 'Account_Name','Account_Name.Account_Name','Account_Name.Parent_Account','Account_Name.Parent_Account.Account_Name' from Contacts where (Account_Name.Parent_Account.Account_Name = 'King') limit 1 "
}
Here, Account_Name will give the ID and display field value of Account that the contact is associated with, Account_Name.Account_Name will give the name of the account, Account_Name.Parent_Account will give the ID of the parent account, Account_Name.Parent_Account.Account_Name will give the account name of the parent account. As you can see, you can use the .(dot) operator to retrieve records that are JOINed via lookup fields.
Response
{
"data": [
{
"Account_Name.Parent_Account.Account_Name": "King",
"Account_Name.Parent_Account": {
"name": "King",
"id": "5725767000005679001"
},
"Account_Name.Account_Name": "Zoho",
"Account_Name": {
"name": "Zoho",
"id": "5725767000005329058"
},
"id": "5725767000005329061"
}
],
"info": {
"count": 1,
"more_records": false
}
}Types of JOINs in COQL
COQL supports two types of JOINs based on how and where a lookup field is used in the query:
- Base JOIN
- SELECT JOIN
Base JOIN
A Base JOIN is created when a lookup field is used outside the SELECT column such as in the WHERE, ORDER BY, GROUP BY clauses, or aggregate column.
Example:
{
"select_query": "select id from Contacts where Owner.role is not null"
}
// Owner.role is the Base JOIN used in the WHERE clause
Note
If two lookup fields point to the same module, they will be treated as separate JOINs in the Base JOINs.
Example:
{
"select_query": "select id from Contacts order by Owner.role, Created_By.profile"
}
// Owner and Created_By are two lookup fields pointing to the Users module used in the Base JOIN. In this case, COQL will treat them as two separate JOINs.
SELECT Column JOIN
A SELECT column JOIN is created when a lookup field is used only in the SELECT column and not in the WHERE, ORDER BY, GROUP BY clauses, or aggregate column.
Example:
{
"select_query": "select Owner.profile, Created_By.role from Contacts where id is not null"
}
// Owner.profile and Created_By.role lookup fields are used only in the SELECT column. Hence, this is considered a SELECT column JOIN.
Note
If two lookup fields in the SELECT clause point to the same module, they do not create multiple JOINs. COQL optimizes this by treating them as a single JOIN.
Example: In the above SELECT column JOIN query, both lookup fields point to the same module (Users module), so COQL treats it as a single JOIN.
Nested JOINs Behaviour
If your query contains nested JOINs like lookup_field_1.lookup_field_2.field, then:
- The first JOIN (lookup_field_1.lookup_field_2) becomes the Base JOIN.
- The second JOIN (lookup_field_2.field) is treated as either:
- A Base JOIN if it is used in places such as the WHERE, ORDER BY, GROUP BY clauses, or in an aggregate column.
- A SELECT column JOIN if it is used only in the SELECT column.