Join Operator in Kusto Query Language | How to Do inner join ,Left Join, Right Join, Full Outer Join in Kusto

Topic: Join Operator in Kusto Query Language.

In this article, we are going to learn about a different types of joins in Kusto, we are going to learn about the inner join left join, and right join full outer join and some other types of joins will be learned in Kusto Query Language. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 // .drop table Customer  
 // .drop table Order  
 .create table Customer(CustomerId:int, Name:string)   
   
  .ingest inline into table Customer <|  
 1,Aamir  
 1,Raza  
 2,Lisa  
 3,steve  
   
 .create table Order(OrderId:int, OrderName:string,CustomerId:int)   
 .ingest inline into table Order <|  
 1, 2 Laptops,1  
 1, 2 Computers,1   
 2, 10 TV ,2  
 3,10 cell phones,4  
   

 // By default the type of Join is innerunique  
 Customer   
 | join Order on CustomerId  
   
 Customer  
 | join kind=innerunique Order on CustomerId  
   
 //using the column names  
 Customer  
 | join kind=innerunique Order on $left.CustomerId==$right.CustomerId  
   
 // if we want to get the Records for other left record, then we can specify  
 Customer  
 | join kind=innerunique Order on $left.CustomerId==$right.CustomerId  
 | where Name=="Raza"  
   
   
 // Inner Join will give us all the records  
 Customer  
 | join kind=inner Order on $left.CustomerId==$right.CustomerId  
   
 // show all records from Left , even no match in Right table  
 Customer  
 | join kind=leftouter Order on $left.CustomerId==$right.CustomerId  
   
 //show all records from right, even no matching records in left   
 Customer  
 | join kind=rightouter Order on $left.CustomerId==$right.CustomerId  
   
 //show all records from both tables , matching and not matching   
 Customer  
 | join kind=fullouter  Order on $left.CustomerId==$right.CustomerId  
   
   
 //show all records from left table which have matching records in right table- display only left table columns  
 Customer  
 | join kind=leftsemi Order on $left.CustomerId==$right.CustomerId  
   
   
 //show all records from right table which have matching records in right table - display only right table columns  
 Customer  
 | join kind=rightsemi  Order on $left.CustomerId==$right.CustomerId  
   
 //leftanti -- the records that does not match with right table but does exists in left table  
 Customer  
 | join kind=leftanti  Order on $left.CustomerId==$right.CustomerId  
   
   
   
 //rightanti -- shows the records from right table which does not exists in left table  
 Customer  
 | join kind=rightanti Order on $left.CustomerId==$right.CustomerId  
   
   

Video Demo: Join Operator in Kusto Query Language | How to Do inner join ,Left Join, Right Join, Full Outer Join in Kusto