Basic introduction to KQL

KQL is a query-only language with (IMO) a more user-friendly and flexible syntax than SQL. There are some similarities however:

• KQL expects to operate on tabular data; i.e. data is held in a two dimensional table consisting of rows and columns where columns have a type (text/number/boolean etc) and are all the same length.
• More complex operations can be performed by joining separate tables. For example you could join a table of products to a table of reviews by product-id then calculate the average rating by product category.

KQL allows for flexible and powerful queries using a "pipe" syntax...

Some examples

products | where Description contains 'laptop' and Price < 2000  | order by Price | take 10

products | extend PriceTier=bin(Price,500) | summarize count() by Category,PriceTier

products | where LaunchDate > date(1 jan 2023)

products | where Category=='PC' | join reviews on ProductId | summarize Rating=avg(Score) by ProductId | order by Rating

Queries can span multiple lines and by convention the pipe operator is placed at the beginning of each line.

products 
| where Category=='PC' 
| join reviews on ProductId 
| summarize Rating=avg(Score) by ProductId 
| order by Rating

Columns can be added using the extend operator

products | extend PriceToNearest100=bin(Price,100)

A subset of columns can be selected for output using the project operator

products 
| extend PriceToNearest100=bin(Price,100) 
| project Name,Price,PriceToNearest100