Tutorial: join data from multiple tables

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Joining data from multiple tables lets you analyze data across sources and create relationships between data points. In the Kusto Query Language (KQL), use the join and lookup operators to combine data across tables.

In this tutorial, you learn how to:

Prerequisites

Run the queries in a query environment that has access to the sample data. Use one of the following:

Use the join operator

The Samples database has two related storm event tables: StormEvents and PopulationData. In this section, you join them to analyze data that's not possible with one table alone.

Understand the data

Use the take operator to see what data each table contains.

StormEvents 
| take 5

The following table shows only six of the 22 returned columns.

PopulationData 
| take 5

Output

Both tables have a State column. StormEvents has many more columns, and PopulationData has one other column with the state's population.

Join the tables

Join PopulationData with StormEvents on State to calculate total property damage per capita by state.

StormEvents
| summarize PropertyDamage = sum(DamageProperty) by State
| join kind=innerunique PopulationData on State
| project State, PropertyDamagePerCapita = PropertyDamage / Population
| sort by PropertyDamagePerCapita

Add | render columnchart to the query to visualize the result.

If the columns have different names (for example, StormEvents has State and PopulationData has StateName), specify the join as:

StormEvents
| join kind=innerunique PopulationData on $left.State == $right.StateName  

$left refers to the left (outer) table in the join: StormEvents. $right refers to the right (inner) table: PopulationData.

Use the lookup operator

The lookup operator optimizes queries that enrich a fact table with data from a dimension table. It extends the fact table with values from the dimension table. By default, the system assumes the left table is the larger fact table and the right table is the smaller dimension table. This default is the opposite of the join operator's assumption.

The help cluster includes a database named ContosoSales with sales data. The following query uses lookup to merge the SalesFact and Products tables to return total sales by product category.

SalesFact
| lookup Products on ProductKey
| summarize TotalSales = count() by ProductCategoryName
| order by TotalSales desc

Output

Join query-generated tables

Join results from the same table.

Suppose you want a list of states that have both lightning and avalanche events. Use the join operator to merge rows from two queries that return distinct states for each event type on the State column.

StormEvents
| where EventType == "Lightning"
| distinct State
| join kind=inner (
    StormEvents 
    | where EventType == "Avalanche"
    | distinct State
    )
    on State
| project State

Output

Related content

• Learn about different kinds of join operator.
• Perform cross-database and cross-cluster queries.
• Follow the Create geospatial visualizations tutorial.