Joins and lookups

Correlating two datasets, and the anti-join you will use more than you expect.

join correlates two datasets on a shared key. The left side is buffered in memory, so put the smaller side on the left — usually an aggregate you just built.

LSQL

read top to bottom

1['deploys']
2| where _time > ago(6h)
3| project service, version, deployed_at = _time
4| join kind=inner ['api-gateway-prod'] on service
5| where _time > deployed_at
6| summarize errors = countif(status >= 500), requests = count() by service, version
7| extend error_rate = round(100.0 * errors / requests, 2)
8| order by error_rate desc

Join kinds

`kind=`	Keeps	Use it for
`inner`	Matches on both sides	Enriching events with metadata
`leftouter`	All left rows, nulls where unmatched	Optional enrichment
`leftanti`	Left rows with no right match	What is missing
`rightanti`	Right rows with no left match	The mirror of the above
`leftsemi`	Left rows that matched, without right columns	Filtering by existence

The anti-join

"Which requests started but never finished" is not a filter — it is an absence. leftanti is the operator for absence, and hand-rolling it with !in over a subquery is both slower and wrong at scale.

Requests with no matching completion event

read top to bottom

1['api-gateway-prod']
2| where _time > ago(1h) and message has "request.start"
3| project ['trace.id'], service, started = _time
4| join kind=leftanti (
5    ['api-gateway-prod']
6    | where _time > ago(1h) and message has "request.end"
7    | project ['trace.id']
8  ) on ['trace.id']
9| summarize stalled = count() by service
10| order by stalled desc

Always bound both sides by time

A join without a _time filter on both sides reads the full retention window twice. It is the fastest way to hit query_timeout.

PreviousStrings and regex NextWorked examples