How to use Union Operator in Kusto Query Language | Kusto Query Language

Topic: How to use Union Operator in Kusto Query Language 

In this article we are going to talk about union operator in Kusto and how it work there are a little changes or there are small differences of  a union operator in Kusto as compared to the SQL where you have uh less or more columns. 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.

 create tables  
 .create table TotalSaleEurope (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,Region : string  
   )  
      //Insert data  
       .ingest inline into table TotalSaleEurope <|  
 12,Aamir,Nawaz,Cell Phone,2,1200,2015-03-03,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Europe  
 // Using Search operator  
 .create table TotalSaleAsia (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
      //Insert data  
       .ingest inline into table TotalSaleAsia <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 14,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,South America  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 // Using Search operator  
 .create table TotalSaleAsia (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
      //Insert data  
       .ingest inline into table TotalSaleAsia <|  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 // Using Search operator  
 .create table TotalSaleEurope (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
      //Insert data  
       .ingest inline into table TotalSaleEurope <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 // UNION OPERATOR  
 //Takes two or more tables and returns the rows of all of them.  
 //Union Multiple tables   
 // Table1 | union Table2,Table3,Table4...  
 TotalSaleEurope  
 TotalSale  
 | union TotalSaleAsia  
 //Using kind=outer -- > outer - (default). The result has all the columns that occur in any of the inputs. Cells that weren't defined by an input row are set to null.  
 union kind=outer withsource="TableName"  
 TotalSaleAsia,TotalSaleEurope,TotalSale  
 //Using kind=inner --inner - The result has the subset of columns that are common to all of the input tables.  
 union kind=inner withsource="TableName"  
 TotalSaleAsia,TotalSaleEurope,TotalSale  
 //Union Tables with Different Listof Columns  
 TotalSale  
 | union TotalSaleAsia,Customer  
 //Union Different Result Sets  
 TotalSale  
 | where Region=="Asia"  
 | union TotalSaleAsia  
 | union TotalSaleEurope   
 //Union Multiple Tables with specified string value  
 union TotalSale* | where * has "aamir"  
 //Add Table Name in output  
 union withsource="SourceTable" TotalSale* | where * has "aamir"  
 //Add a Source Table Name to output query   
 TotalSale   
 | union withsource="SourceTable" TotalSaleAsia,TotalSaleEurope  
 //Union Multiple Result Sets  
 union withsource="SourceTable"  
 (TotalSale | project SalePersonFName,SalePersonLName,Region) ,  
 (TotalSaleAsia | project SalePersonFName,SalePersonLName,SoldPrice),  
 (TotalSaleEurope | project SalePersonFName,SalePersonLName)   
 // Apply further Filter to your Union results  
 union withsource="SourceTable"  
 (TotalSale | project SalePersonFName,SalePersonLName) ,  
 (TotalSaleAsia | project SalePersonFName,SalePersonLName),  
 (TotalSaleEurope | project SalePersonFName,SalePersonLName) //| where SalePersonFName =="aamir"  
  | count  
 //Get the Table Name instead of union_age0,Union_arg1...  
 let TotalSaleDrv=view() {TotalSale | project SalePersonFName,SalePersonLName}; // Add a Column that does not include will produce results for only that dataset.  
 let AsiaTotalSaleDrv=view() {TotalSaleAsia | project SalePersonFName,SalePersonLName,Region};  
 let EuropTotalSaleDrv =view() {TotalSaleEurope | project SalePersonFName,SalePersonLName,Region};  
 union withsource=TableName TotalSaleDrv,AsiaTotalSaleDrv,EuropTotalSaleDrv  

.

Video Demo: How to use Union Operator in Kusto Query Language