In part 1 we got a pretty good understanding how joins work and what the results are. To make this a complete series we will now work on the remaining join types.
Full Outer
The fullouter join is a join that shows in addition to the inner matches, there’s a row for every row on the left (and/or right), even if it has no match. In that case, the unmatched output cells contain nulls. Our test tables look the same as in part 1…
…we expect now to have matching results from both tables and also showing not matching results as well…
…and the result is there as predicted. Note here “Tim” does not have a matching record in the left table and “Demo” and “Fritz” do not have a matching result in the right table.
Right Outer
The rightouter join is similar to the leftouter join…
…but it uses instead of the left table as leading table, it will use the right table as “master”. Therefore it will show results for every record on the right table including duplicate records and also records that do not have a match in the left table…
…pretty easy, huh?
Right Anti (or Right Anti Semi)
Before we come to the end there are two more joins to test. Rightanti or rightantisemi is the similar as leftanti or leftantisemi but the leading table is the right table….
…this means it will return all the records from the right side that do not have matches on the left side…
…and as expected “Tim” is the record which is not available on the left side.
Right Semi
Finally we come to an end showing the rightsemi join…
…we probably have now a pretty good idea how this join works. It’s counterpart is leftsemi join which means the result will show rows from the right table that have a match in the left table including duplicate records. But only the results from the right table will be in the output…
..et voilà the displayed names are the expected result.
Super, we have discussed all join types in Azure Log Analytics. If you enjoyed these parts or want to add some ideas / comments let me know.
I hope this will help, happy ALA .