The CData Cmdlets PowerShell Module for Zoho Creator supports standard SQL joins like the following examples.

Inner Join

An inner join selects only rows from both tables that match the join condition:

SELECT My_Leaves.Employee_display_value, My_Leaves.Leave_Type_display_value,My_Team_Leaves.Employee_display_value,My_Team_Leaves.Leave_Type_display_value FROM My_Leaves  INNER JOIN My_Team_Leaves ON My_Leaves.ID = My_Team_Leaves.ID

Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:

SELECT My_Leaves.Employee_display_value, My_Leaves.Leave_Type_display_value,My_Team_Leaves.Employee_display_value,My_Team_Leaves.Leave_Type_display_value FROM My_Leaves  LEFT JOIN My_Team_Leaves ON My_Leaves.ID = My_Team_Leaves.ID