Join table in Kusto Query Language (KQL)

In this article, We learn how to join two or more tables in Kusto Query Language (KQL). I will show how to do it, and just follow the steps clearly.

For more updates please do Subscribe via Email:

What is Kusto Query Language (KQL)

Kusto is a query language designed for big data workloads particularly using large amount of data in from things like logs and event sources. Kusto query is a read-only request to process data and return results. KQL is the first party query language for Kusto cluster used by Azure Data Explorer. Kusto Query is only good for pulling or getting data from the data bank.

Read more about Kusto here

This is the sample Tables and Data.

Joining Employee Table to Employ Contacts and display all columns
- Inner Join sample Kusto query

Employee
| join kind=inner EmployeeContact on $left.EmpID == $right.EmpID 
| project EmpID, Firstname, Lastname, Middlename, Status, Postion, PersonalPhoneNo, PersonalEmail, CompanyPhoneNo, CompanyEmail

Output Dataset

For left Join Sample Query

Employee
| join kind=leftouter EmployeeContact on $left.EmpID == $right.EmpID 
| project EmpID, Firstname, Lastname, Middlename, Status, Postion, PersonalPhoneNo, PersonalEmail, CompanyPhoneNo, CompanyEmail

For right Join Sample Query

Employee
| join kind=rightouter EmployeeContact on $left.EmpID == $right.EmpID 
| project EmpID, Firstname, Lastname, Middlename, Status, Postion, PersonalPhoneNo, PersonalEmail, CompanyPhoneNo, CompanyEmail

Joining the Three table together like this.

Employee
| join kind=inner EmployeeContact on $left.EmpID == $right.EmpID 
| join kind=inner EmployeeRate  on $left.EmpID == $right.EmpID
| project EmpID, Firstname, Lastname, Middlename, Status, Postion, PersonalPhoneNo, PersonalEmail, CompanyPhoneNo, CompanyEmail, MonthlyRate, NohoursWork, NoDaysWorks, OvertimeRate

The same KQL is a Inner Join statement. you can use either inner, left or right join state defend of your business role requirements.

All query Above is displaying all columns in the table. this is how you can display the EmpID, Status, Position, CompanyEmail, MonthlyRate.

Employee
| join kind=inner EmployeeContact on $left.EmpID == $right.EmpID 
| join kind=inner EmployeeRate  on $left.EmpID == $right.EmpID
| project EmpID, Status, Postion, CompanyEmail, MonthlyRate

Happy Learning..

Thank you for visiting my blog site. Hoping you learn more here. please feel free to comment and suggest if there is need to enhance and update. thank you.