SQL and KQL Comparison
Last updated
Last updated
If you are familiar with SQL, there are some similarities to KQL. However, SQL is designed to manage structured data in relational databases. KQL, on the other hand, is designed to query extensive amounts of structured, unstructured, and semi-structured data, such as logs and telemetry data, in real-time analytics situations.
Below is a cheat sheet that includes some common SQL queries and their KQL equivalents to help you transition to KQL.
Category
SQL
KQL
Select data from a table
SELECT * FROM table_name
table_name
Select data from a table
SELECT column1, column2 FROM table_name
table_name | project column1, column2
Aggregation/ Grouping
SELECT DISTINCT column FROM table_name
table_name | summarize by column
Aggregation/ Grouping
SELECT AVG(column1), SUM(column2) FROM table_name
table_name | summarize AvgColumn1=avg(column1), SumColumn2=sum(column2)
Aggregation/ Grouping
SELECT COUNT(\*) FROM dependencies GROUP BY name HAVING COUNT(\*) > 3
table_name | summarize Count = count() by name | where Count > 3
Select data from a table
SELECT TOP 10 * FROM table_name
table_name | take 10
Filtering data
SELECT * FROM table_name WHERE condition
table_name | where condition
Top n by measure
SELECT * FROM table_name ORDER BY column
table_name | order by column asc
Top n by measure
SELECT TOP 10 * FROM table_name
table_name | top 10 by *
Join
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column
table_name | join kind=inner table2 on $left.column == $right.column
Select data from a table
SELECT name, resultCode FROM dependencies
table_name | project name, resultCode
Subquery
SELECT column1 FROM (SELECT * FROM table_name) AS subquery
let subquery = table_name; subquery | project column1;
Comparison operator (date)
SELECT * FROM dependencies WHERE timestamp > getdate()-1
table_name | where timegenerated > ago(1d)
It is worth noting that KQL is primarily used within Microsoft services such as Azure Data Explorer, Microsoft Defender, and Microsoft Sentinel for log analytics and telemetry data. It is designed to perform fast and efficient data exploration and analysis, especially with time-series data. While the cheat sheet above covers some basic operations, don't forget that KQL has a wide range of functions and capabilities for more advanced scenarios.