Azure Log Analytics – Testing JOINs Part 1

Azure Log Analytics offers you a powerful language to analyze your data. I am not a SQL query specialist but as far I can tell there are many similarities between SQL language and (New) Azure Log Analytics query language. One cool thing we can do is using joins. If you look up what a join is in SQL on Wikipedia it says…

A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS.

In Azure Log Analytics it is pretty much the same although the join type have different names. In this post I would like to show how you can easily test the documented joins in Azure Log Analytics. To test the scenarios we need tables first. The easiest and most transparent way is probably if we create our well defined tables. Of course you could use any table like Heartbeat etc. to test your join operators but I found it hart to reproduce any result. Luckily there is a way in Azure Log Analytics to create a data table. So let’s create two data tables, the first table is called left table and the second table is called right table. If you follow the Microsoft documentation and you are creating a dummy data table it would look like this….

…so what is going on here? We are using the let statement to assign the datatable operator which has two arguments ID and Name. Then we define the content separated by commas. The same approach we repeat for the right (second) table…

…at this time we created the foundation for testing Azure Log Analytics joins.

Inner Join

The first join is the inner join…

…as you can see we define the type of join kind=inner and join the left table on the ID. Inner join does the following, it matches records from both tables without removing duplicate records. So the result will look like this…

…as we can see “Stefan” does not appear on the list, because it has no matching record on the right table. If there are duplicate entries on either side, each entry will be in the output like “Simona” or “Peter”.

Inner Unique

The next join works on the same tables we only change to kind=innerunique…

…innerunique join will match the first values of the matched column in the left (first) table that are found, and duplicate values are removed from the left table…

“Peter” appears only once because duplicates are removed from the left table. “Simona” appears twice because it exists in the right table and will not be removed per definition. “Max” exists in both tables, so it must appear.

Left Outer

Leftouter join will show more results as we will see in a minute. The tables are the same as before…

…in addition to the inner join, leftouter matches, the results including a record for every record on the left table, even if it has no match…

…the results which have matches on both sides will appear and then if there are no matches on the right table, the records “Demo”, “Fritz” and “Stefan” will be in the output as well.

Left Anti (or Left Anti Semi)

The second last join is called leftanti and will be e.g. used in finding differences in table…

…again we only change the kind=leftanti expression and the rest stays the same as before. This join will return all the records from the left side that do not have matches from the right. This means I could use this join to find content which does not exist in the other table…

…as we can see neither “Demo”, “Fritz” nor “Stefan” exists in the right table.

Left Semi

The last join is leftsemi is the opposite of leftanti in a kind of sense….

…it will return rows from the left table that have a match in the right table. This means there are matching results in the right table, but these will not be displayed only the one in the left table…

…as the result shows the matching records for both tables but only the data from the left table appears in the output including duplicate records.

In part 2 we will discuss the rest of the joins. Stay tuned…