OnCallReady

Lesson 24.9 · Azure III: Monitor, KQL & Cost · 13 min read

KQL 3: join, union and let for tables

In plain words

Imagine two class lists. One says which pupil sits at which table; the other says which table is by the window. To find out which pupils sit by the window, you put the lists side by side and match them on table number. If you're careless, some pupils vanish because their table isn't on the second list, or appear twice because a table is written down twice.

That's join. KubePodInventory says which pod ran on which node, KubeNodeInventory says which node belongs to the spot pool; join kind=leftouter (nodes) on Computer puts them side by side. The trap: KQL's default join is innerunique, which silently keeps only one row per key from the left. So always write the kind. union stacks tables instead, and let gives a sub-query a name to keep long queries readable.

Payments is failing. The failed requests are in AppRequests, which pod served them is in KubePodInventory, which node that pod ran on and whether the node is a spot node (23.25) is in KubeNodeInventory. No single table answers "were the failing pods on spot nodes?". Combining tables does.

What you need to know already: 24.3 (where, project, let), 24.5 (summarize, arg_max), 23.25 (spot node pools and eviction), 15.26 (labels on Kubernetes objects).

join: put two tables side by side

A | join (B) on Key matches each row of A (the left side) with the rows of B (the right side) that have the same value in the column Key, and glues them into one wider row. The same idea as a SQL join, or a lookup in a spreadsheet.

The example first names two small tables with let (pods: which node each payments pod ran on; nodes: each node's latest Labels, the Kubernetes labels as JSON text), then joins them on Computer (the node name) and adds a column spot that is true when the labels contain "spot".

let pods = KubePodInventory
  | where TimeGenerated > ago(1h) and Namespace == "payments"
  | distinct Name, Computer;
let nodes = KubeNodeInventory
  | where TimeGenerated > ago(1h)
  | summarize arg_max(TimeGenerated, Labels) by Computer;
pods
| join kind=leftouter (nodes) on Computer
| project Name, Computer, spot = Labels has "spot"
Name                          Computer                      spot
----------------------------  ----------------------------  -----
payments-api-6b7f8d9c4-h2k9m  aks-spot-31415926-vmss000000  True
payments-api-6b7f8d9c4-q7x4n  aks-spot-31415926-vmss000001  True
payments-api-6b7f8d9c4-t5v8b  aks-spot-31415926-vmss000002  True
payments-api-6b7f8d9c4-z3c6w  aks-user-31415926-vmss000002  False

Join kinds

Kind says what happens to rows that match and rows that do not:

innerunique   DEFAULT. Deduplicates the LEFT side on the key, then inner-joins.
inner         every matching pair of rows
leftouter     every left row; right columns empty where nothing matched
leftanti      left rows with NO match on the right      ("which pods have no node row?")
leftsemi      left rows that HAVE a match, left columns only

The default is not inner. innerunique keeps one arbitrary left row per key before joining. Join 60 per-minute pod snapshots against nodes with the default and you get one row per pod - which looks like a clever dedup until the day you wanted all 60. State the kind every time: join kind=inner.

Keys

The key is the column (or columns) the two sides are matched on. $left and $right name the two sides when the key column has a different name on each:

| join kind=inner (nodes) on Computer                       // same column name
| join kind=inner (nodes) on $left.Computer == $right.Node  // different names

Columns that exist on both sides come out with a 1 suffix from the right (TimeGenerated1, Computer1). project away what you do not need.

Keep the right side small

The right side of a join should be the smaller table and already filtered and summarised - inventory tables reduced to "latest row per key" with arg_max, a short time window. Joining two raw hour-long tables multiplies rows and is slow. Often a summarize over a union is better than a join.

union

union KubeEvents, ContainerLogV2
| where TimeGenerated > ago(30m)
| where * has "payments"

Where join puts tables side by side, union stacks them one under the other (columns merged by name; a column one table lacks is empty for its rows). where * has "x" means "any column has the term x". Useful for "everything that mentions X in the last half hour". (The lab implements union of named tables; where * has across all columns is real KQL but not simulated - filter on a column you know.)

let for tables

A tabular expression is anything that produces a table: a table name, or a table followed by operators. let name = <tabular expression>; names such a sub-query; the final statement uses the name like a table. It is how long incident queries stay readable - the chapter's hunts are all built this way.

The enrichment pattern, step by step

  1. Build the small side: the latest attribute per key.
let nodePool = KubeNodeInventory
  | where TimeGenerated > ago(1h)
  | summarize arg_max(TimeGenerated, Labels) by Computer
  | extend pool = tostring(parse_json(Labels)[0].agentpool)   // the node pool name, from the labels JSON (24.11)
  | project Computer, pool;
  1. Join it onto the big side, leftouter so nothing silently disappears:
KubePodInventory
| where TimeGenerated > ago(1h)
| summarize arg_max(TimeGenerated, PodStatus, Computer) by Namespace, Name
| join kind=leftouter (nodePool) on Computer
| summarize pods = count() by pool, PodStatus

This is called enrichment: adding columns from a small "facts about each thing" table onto a big table of events.

  1. Check the row count before and after. If it grew, the right side had duplicate keys; if it shrank with an inner join, keys did not match (case, trailing spaces, a short Computer name vs a full DNS name, the FQDN from Ch 8-9).

lookup (not simulated here)

Real KQL also has lookup - a join specialised for "enrich from a small table of facts" (a dimension table), which behaves like leftouter by default and has no deduplication surprises. Where you would write join kind=leftouter against a small table, lookup is the idiomatic choice.

What you can now do

Why it helps

Incidents cross tables: the 5xx is in AppRequests, the pod in KubePodInventory, the node in KubeNodeInventory, the eviction in KubeEvents. Joining them is how you get from "payments is failing" to "three of four payments replicas were on spot nodes that were evicted at 09:20", which is the chapter's incident and a real pattern in production.

Joins are also where queries get slow and wrong. A raw hour of two inventory tables multiplies rows; the default join kind silently dedups; a case difference in a key makes rows disappear from an inner join. Knowing to shrink the right side with arg_max first, use leftouter so nothing vanishes, and check row counts before and after, is what makes your incident conclusions trustworthy.

FAQ

What is the default join kind and why does it matter?

innerunique: it deduplicates the left side on the join key, keeping one arbitrary row per key, then does an inner join. If you join 60 per-minute pod snapshots to nodes with the default, you get one row per pod, which can look like a clever dedup until you needed all 60, or the arbitrary row was the wrong one. State the kind every time: inner, leftouter, leftanti or leftsemi.

When should I use leftouter?

When enriching: you have a main table and want to add attributes from another, without losing rows that have no match. Rows without a match keep empty right-side columns, so you can see and count them. With inner, unmatched rows silently disappear, which hides problems like a key mismatch, or a pod whose node no longer reports. leftanti then shows exactly the left rows with no match.

Why do I get columns like Computer1?

When both sides of a join have a column with the same name, the right side's copy gets a 1 suffix: TimeGenerated1, Computer1. The join key column appears once if you used on Computer; with $left.X == $right.Y both keep their names. Use project or project-away after the join to keep only what you need, which also makes the output readable.

Why is my join so slow?

Probably because both sides are large and unfiltered, such as two raw inventory tables over an hour, so the engine matches many rows against many rows. Filter both sides by time, summarise the right side to one row per key with arg_max, and put the smaller table on the right. Often a single summarize over a union of the tables does the job without a join at all.

What is the difference between join and union?

join puts rows from two tables side by side, matching them on a key, so you get wider rows combining columns from both. union stacks rows from several tables into one taller result, merging columns by name and leaving missing ones empty. Use join to enrich, "which node was this pod on"; use union to gather, "everything that mentioned payments in the last half hour, from events and logs".

In an interview Mid

What are the join kinds in KQL, and what is the trap in the default?

A | join kind=<kind> (B) on Key matches rows of the left side with rows of the right side that have the same key.

Practice: keep the right side small and pre-reduced (summarize arg_max(TimeGenerated, Labels) by Computer in a let), use $left.A == $right.B when key names differ, project away the 1-suffixed duplicates, and compare row counts before and after. union is the other combinator: it stacks tables instead of putting them side by side.

Also asked: How would you find which node pool the failing pods of a service were running on? · What is the difference between join and union in KQL? · How do you keep a KQL join fast on large tables?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.