What is the conventional way of checking whether something Exists in another table in Application Insights Analytics Query Language?

Question

I want to limit requests by a specific associated dependency by name. I tried using a leftsemi join but that didn't seem to work as I expected because it game me the same results as my inner join.

requests
| where timestamp >= ago(24h)
| join kind=leftsemi (
    dependencies
    | where name contains "MYDATABASENAME" 
) on operation_Id 
| summarize count() by tostring(parseurl(url).Path)
| order by count_ desc

I'm looking at the the where-in statement next but I'm still unsure whether this is sort of the expected way to do what what typically be an exists statement in T-SQL.

Answer 1

Actually, in order to get the where-in semantics you should use inner join. From the documentation of join (at the kind=inner section):

There's a row in the output for every combination of matching rows from left and right.

In addition, since there's a limit on the size of the returned table, you might want to limit the right side of the join like this:

requests 
| where timestamp >= ago(24h)
| join kind=inner (
    dependencies
    | where name contains "MYDATABASENAME" 
    | project operation_Id
) on operation_Id 
| summarize count() by tostring(parseurl(url).Path)
| order by count_ desc

Answer 2

You should be able to use let statement to achieve this.