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 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": {
                    "id": "3652397000000190102"
                },
                "Account_Name.Account_Name": "Zylker",
                "Account_Name": {
                    "id": "3652397000000624046"
                },
                "id": "3652397000000269089"
            }
        ],
        "info": {
            "count": 1,
            "more_records": true
        }
    }

What is the Base JOIN?

• If a query contains any lookup_field.{any_field} in any clause other than the SELECT column, then it will be treated as BaseJOIN. 

  {
   "select_query" : "select id from Contacts where id is not null order by Owner.role, Created_By.profile"
  }
                                                      

• If there are more than one lookup_fields (lookup_field_1.lookup_field_2.{any_field}) in a query, then the first associated lookup_field (lookup_field_1) will be the first baseJOIN. The second JOIN will be considered either a Base JOIN or a SELECT Column JOIN (Refer to the SELECT Column JOIN detailed below) based on the above mentioned point (refer to Point 1).

  {
  "select_query" : "select id from Contacts where id is not null order by Owner.role.id"
  }
                                                      

Whats is SELECT Column JOIN?

• If the lookup field is not part of the Base JOIN and lookup_field.{any_field} present only in the SELECT column, a SELECT column JOIN will be applied.

• If two lookup fields point to the same module, for example,  Owner.role and Created_By.profile, where both Owner and Created_By point to the Users module, they will be treated as a singleJOIN in the SELECT column.

  {
  "select_query" : "select Owner.profile, Created_By.role from Contacts where id is not null"
  }