[go: up one dir, main page]
Scribd
Upload
223 views2 pages

Sub Query in KQL

The document explains various ways to use subqueries in Kusto Query Language (KQL) for efficient data filtering, joining, and transformation. It covers techniques such as using 'let' for common table expressions, 'datatable()' for temporary datasets, 'materialize()' for performance optimization, and filtering with subqueries. Each method is illustrated with examples to demonstrate its application in querying sales data and student scores.

Uploaded by

saiakkina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
223 views2 pages

Sub Query in KQL

The document explains various ways to use subqueries in Kusto Query Language (KQL) for efficient data filtering, joining, and transformation. It covers techniques such as using 'let' for common table expressions, 'datatable()' for temporary datasets, 'materialize()' for performance optimization, and filtering with subqueries. Each method is illustrated with examples to demonstrate its application in querying sales data and student scores.

Uploaded by

saiakkina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

In Kusto Query Language (KQL), you can use subqueries (also called subtables) in

several ways to filter, join, or transform data efficiently. Here are different
ways to use subqueries in KQL.

1. Using let for a Subquery (Common Table Expression)


Scenario:
You have a table SalesData and want to find total sales per region but only for
regions where sales exceed 10,000.

KQL Query:
kusto
Copy
Edit
let HighSalesRegions = SalesData
| summarize TotalSales = sum(Sales) by Region
| where TotalSales > 10000;

HighSalesRegions
| join kind=inner (SalesData) on Region
Explanation:
The subquery (let HighSalesRegions) filters regions where total sales exceed
10,000.
The main query then joins the result with SalesData to get detailed sales records
for those regions.
2. Using datatable() as an Inline Subquery
Sometimes, you may need to use a temporary dataset as a subquery.

Example:
kusto
Copy
Edit
let temp_table = datatable(StudentId:int, Score:int)
[
101, 85,
102, 78,
103, 92
];

temp_table
| where Score > 80
Explanation:
The datatable() function creates a temporary table for testing.
The main query filters students who scored above 80.
3. Using materialize() for Performance Optimization
If you're using a subquery multiple times, you can store it using materialize() to
improve performance.

Example:
kusto
Copy
Edit
let FilteredData = materialize(
SalesData
| where Year == 2024
);

FilteredData
| summarize TotalSales = sum(Sales) by Region
Why materialize()?
Unlike let, materialize() caches the subquery’s results, preventing re-execution.
4. Using where with a Subquery
You can filter records based on values from another query.

Example:
kusto
Copy
Edit
SalesData
| where Region in (
SalesData
| summarize TotalSales = sum(Sales) by Region
| where TotalSales > 10000
| project Region
)
Explanation:
The subquery finds high-sales regions.
The outer query filters SalesData to include only those regions.

You might also like