How to Join Tables in KQL: Step-by-Step Guide

Combining data from two tables is one of the most common tasks in data analysis — and in KQL you do it with the join operator. If you work with Real-Time Analytics in Microsoft Fabric, joining telemetry with reference tables (sensors, customers or products) is essential. This guide shows you how to run a KQL join, step by step, with examples you can copy and run right away.

Prerequisites

• A KQL Queryset in a Microsoft Fabric workspace (or any Azure Data Explorer editor).
• Basic KQL knowledge: using the | pipe and the project operator.
• No data loading required: the examples build the tables with datatable.

Step 1: Create two sample tables

To practise without depending on pre-loaded data, we'll generate two tables on the fly. The datatable operator creates temporary tables directly in the query, ideal for testing ideas without touching your real data. One holds the sensors and their location; the other, the temperature readings. Notice that sensor 1 has two readings and that there is a reading from sensor 4, which does not appear in the sensors table.

let Sensores = datatable(SensorId: int, Local: string)
[
    1, "Lisboa",
    2, "Porto",
    3, "Faro"
];
let Leituras = datatable(SensorId: int, Temperatura: real)
[
    1, 21.5,
    2, 19.8,
    1, 22.1,
    4, 30.0
];
Leituras
| take 10

Run the query to confirm that the Leituras table has four rows.

Step 2: Your first join

Let's attach each reading to its sensor's information. The table on the left of join is Leituras; the table in parentheses is the right side; and on SensorId points to the shared column. Read the join from left to right: for each row in Leituras, KQL looks for a match in Sensores. We use kind=inner to keep only the rows that exist in both tables.

Leituras
| join kind=inner (Sensores) on SensorId
| project SensorId, Local, Temperatura

The result has three rows: the two readings from sensor 1 (Lisboa) and the one from sensor 2 (Porto). Sensor 4 disappears because it does not exist in the Sensores table.

Step 3: Beware the default join (the most common mistake)

If you forget the kind=, KQL assumes innerunique — and the result may surprise you. This join kind first removes duplicate keys from the left table before joining. The behaviour exists for performance reasons, but it is a frequent source of confusion.

Leituras
| join (Sensores) on SensorId
| project SensorId, Local, Temperatura

Now only two rows appear: one of sensor 1's readings was dropped. That's why you should always set the kind explicitly, so you don't lose data by accident.

Step 4: Keep unmatched rows with leftouter

What if you also want to see sensor 4, even without a known location? Use kind=leftouter, which keeps every row from the left table, matched or not.

Leituras
| join kind=leftouter (Sensores) on SensorId
| project SensorId, Local, Temperatura
| sort by SensorId asc

Now you have four rows. The sensor 4 row shows an empty Local, signalling there was no match.

Tip: the left side of the join is also what the engine loads into memory. In queries over large data, put the smaller table on the left — but remember this also changes which rows leftouter preserves.

Check the result

To confirm you've understood each join kind, compare the counts: inner returns 3 rows, the default innerunique returns 2, and leftouter returns 4. It's a good habit to always check the row count after a join: unexpected changes in the count are usually the first sign of the wrong join kind.

Conclusion

You can now join tables in KQL, choose the right join kind, and avoid the innerunique trap. The next step is to try the lookup operator, an optimised alternative for enriching data from a small reference table. Which reference table will you join to your telemetry first?