Tutorial: Join data from multiple tables

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

Joining data from multiple tables allows for a more comprehensive analysis by combining information from different sources and creating new relationships between data points. In the Kusto Query Language (KQL), the join and lookup operators are used to combine data across tables.

In this tutorial, you'll learn how to:

Prerequisites

To run the following queries, you need a query environment with access to the sample data. You can use one of the following:

Use the join operator

There are two tables in the Samples database related to storm events. One is called StormEvents and the other is called PopulationData. In this section, you'll join the tables to perform data analysis that wouldn't be 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 6 of the 22 returned columns.

PopulationData 
| take 5

Output

Both tables contain a State column. The StormEvents table has many more columns, and the PopulationData has only one other column that contains the population of the given state.

Join the tables

Join the PopulationData table with StormEvents on the common State column to find the total property damage caused by storms 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.

Use the lookup operator

The lookup operator optimizes the performance of queries where a fact table is enriched with data from a dimension table. It extends the fact table with values that are looked up in a dimension table. For best performance, the system by default assumes that the left table is the larger fact table, and the right table is the smaller dimension table. This is exactly opposite to the assumption that's used by the join operator.

In the help cluster, there's another database called ContosoSales that contains sales data. The following query uses lookup to merge the SalesFact and Products tables from this database to get the total sales by product category.

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

Output

Join query-generated tables

Joins can also be done based on query results from the same table.

Say you want to create a list of states in which both lightning and avalanche events occurred. Use the join operator to merge the rows of two tables—one containing data on lightning events and the other containing data on avalanche events—based 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
• Learn how to perform cross-database and cross-cluster queries
• Follow the create geospatial visualizations tutorial