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

2 comments:

  1. Brothers/Sister's. Are you looking for loan to finance your large or small business,we'll help you get the large amount of loan you desire for your business,we offer first class business and commercial loan to enable small scale business attain success in obtaining start up or refinance their business Bad credit rating accepted and poor business performance are accepted.apply today

    Are you a business man or woman? Do you need funds to start up your own business? Do you need loan to settle your debt or pay off your bills or start a nice business? Do you need funds to finance your project? We Offers guaranteed loan services of any amount and to any part of the world for (Individuals, Companies, Realtor and Corporate Bodies) at our superb interest rate of 3%. For application and more information send replies to the

    via email ronniefinancehome247@gmail.com
    Whats App +91 93118 56893
    LOAN APPLICATION FORM
    Name:
    Sex:
    Address:
    Occupation:
    State:
    Country:
    Phone Number:
    Loan Duration:
    Loan Needed:
    Have you applied before?:
    Email us ronniefinancehome247@gmail.com
    Whats-App +91 93118 56893
    Dr. Mark Thomas

    ReplyDelete
  2. Thank you for the useful information. It is beneficial to me. Could you please provide me with more information? I read the post A Very Merry Christmas on your blog. In any case, I was looking forBest assignment help online. Your blog is excellent. I found a lot of fascinating information in there. I really appreciate you sharing. I hope you'll continue to provide news updates.

    ReplyDelete