How to use joins in Kusto

In the context of databases, a join is a way to combine data from two or more tables in a database. Tables in a database are often related to each other in some way, and a join allows you to retrieve data from multiple tables in a single query.

There are several flavors of joins in Kusto. We are going to going to explore some joins. In this article, we are joining the Orders table with the Customer table.

Let’s look at a selection from the “Orders” table:

Orders table

Let’s also look at a selection from the “Customers” table:

Customers table

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.

More information about Kusto joins can be found in this article by Microsoft join operator — Azure Data Explorer | Microsoft Learn. We’re going to go through examples of the different joins here.

The Order table is on the left and the Customer table is on the right.

Orders
| join kind=fullouter Customers on CustomerID

FullOuter Join

Returns all the records from both left and right sides. Unmatched cells contain nulls.

Note that order 10048 and customer 99 that doesn’t have a match are included in the full outer join. In addition, there are duplicates for each of order 88 for each order and each customer.

LeftOuter Join

Returns all records from the left side and only matching records from the right side.

Note that all the rows on the left table are returned.

LeftSemi join

Returns all the records from the left side that have matches from the right side

Note that all the rows that have matches on the left table are returned and the result only contains columns from the left side only.

LeftAnti / LeftAntiSemi join

Returns all the records from the left side that don’t have matches from the right

Note that all the rows that DON’T have matches on the left table are returned and the result only contains columns from the left side only

RightOuter join

Note that all rows from the right side are returned.

RightSemi

Returns all the records from the right side that have matches from the left.

Note that all the rows that have matches on the right table are returned and the result only contains columns from the right side only.

RightAnti / RightAntiSemi join

Returns all the records from the right side that don’t have matches from the left.

Note that all the rows that DON’T have matches on the right table are returned and the result only contains columns from the right side only

Inner Join

Returns all matching records from left and right sides

Note that only matching records from both tables are returned. It’s the inverse of the full outer join.

InnerUnique Join

Only one row from the left side is matched for each value. The output contains a row for each match of the left row with rows from the right.

This is the weirdest join. Only one matched value is return if there are duplicates.