Reading width
Wide uses the full column for everything, text, diagrams, code, and exercises. Narrow keeps the standard reading width.
Text size
Scales the body text. Headings and code blocks keep their size.
In this section
KQL Foundations: union and join, innerunique, inner, leftouter and leftanti Joins
Introduction
Few security questions live in one table. A sign-in is in one table and the device it came from in another; a process start is in one table and the inventory record that says what kind of device ran it is in another.
KQL has two ways to bring tables together. union stacks tables of the same kind of record on top of each other, and join matches rows of one table to rows of another by a shared value.
This lesson does both. It stacks the two sign-in tables, then joins a month of process starts to the device inventory four ways, and gets four different answers. One of them is right, one quietly drops almost every row, one multiplies the rows, and one finds the devices the inventory does not know about.
The same 9,578 process starts joined to the same inventory four ways.
This is the ground Module 5 builds on. Every query on the page runs in your browser against the same month.
Putting Sources Side by Side
union stacks tables of one kindEntra ID records sign-ins in more than one table. SigninLogs holds interactive sign-ins, where a person typed a password or approved a prompt. AADNonInteractiveUserSignInLogs holds the sign-ins clients make on a user's behalf, such as a token refreshed in the background. A question about all of a user's sign-ins needs both, and union puts them in one result:
union withsource = Source SigninLogs, AADNonInteractiveUserSignInLogs
| summarize SignIns = count(), Accounts = dcount(UserPrincipalName) by Source
withsource adds a column naming the table each row came from, so the summarize can count each source:
Source SignIns Accounts
------------------------------- ------- --------
SigninLogs 8203 101
AADNonInteractiveUserSignInLogs 13586 94
8,203 interactive sign-ins and 13,586 non-interactive ones, 21,789 in all. Non-interactive sign-ins outnumber interactive ones, which is normal: every open mail client and Teams session refreshes its tokens on its own. A query that read only SigninLogs would see less than half of the month's sign-ins, and an attacker using a stolen token would sit almost entirely in the half it did not read.
union does not match anything. It takes the rows of each table and puts them one after another, with the columns of all of them; a column one table lacks is empty in that table's rows.
That makes it the right tool when the tables record the same kind of event in a similar shape, as the sign-in tables do, and the wrong tool when the question needs a row from one table beside a row from another. That second kind of question needs join.
The accounts column is a reminder that union does not deduplicate. An account that appears in both tables is counted in each source's row, and the two distinct counts cannot be added to get the number of accounts that signed in. That needs one more summarize over the union without the by Source, which counts each account once across both tables.
The withsource column is worth adding to every union, even when nobody asked which table a row came from. When a result surprises you, the first question is usually which source produced the surprising rows, and a union without it cannot answer.
Matching Rows Across Tables
A shared key, and how many rows each side has per keyA join takes two tables, finds rows whose key values match, and puts each matching pair side by side in one row. The key here is DeviceId, which both the process table and the inventory carry. Before joining, it pays to know how many rows each side has for each key, because that decides how many rows the join returns. Look at the inventory first:
DeviceInfo
| summarize Rows = count(), Devices = dcount(DeviceId),
Earliest = min(Timestamp), Latest = max(Timestamp)
The inventory's size, its number of devices and the span of its records:
Rows Devices Earliest Latest
---- ------- ------------------- -------------------
225 15 2026-03-01 00:02:00 2026-03-15 03:55:00
225 rows for 15 devices: DeviceInfo records a snapshot of each device every day, so each device has 15 rows in this window, one per day from 1 March. That is normal for the table and important for the join. The process table, meanwhile, has 9,578 rows on 18 devices, hundreds of rows per device.
So both sides have many rows per key, and that is the situation where a join's behavior matters most. The question being asked is simple: how many process starts happened on devices the inventory knows about, and what does the inventory say about them?
A correct answer can be at most 9,578, one row per process start. Any join that returns more has paired processes with more than one inventory row each; any join that returns fewer has dropped processes.
The key itself deserves a moment too. DeviceId is a stable identifier assigned by Defender, so the same device has the same value in both tables. DeviceName would also seem to work, but names change, can be reused, and are sometimes recorded with and without a domain suffix, so a join on the name can miss matches or make false ones. Join on the most stable identifier both sides share.
Counting each side per key before joining is the single most useful habit in Module 5. It turns the join from something to trust into something to predict: with N rows per key on the left and M on the right, an inner join returns N times M rows for that key. If the prediction and the result disagree, the join is not doing what you think.
The Default Join Drops Rows
join with no kind keeps one left row per keyHere is the join written the way most people first write it, with no kind:
DeviceProcessEvents
| join DeviceInfo on DeviceId
| count
It runs without an error and returns this:
Count
-----
225
225 rows from 9,578 process starts. Almost every process vanished. The reason is the default.
A join with no kind is innerunique, which Microsoft documents as an inner join after removing duplicate keys from the left side: the left table is reduced to one row per DeviceId before matching, and which of a device's rows survives is not guaranteed. 15 surviving process rows, each matched to its device's 15 inventory rows, make 225.
The result is wrong in a way that is hard to see. 225 is a plausible count of something, and nothing in it says that 9,353 rows were thrown away. The kept process for each device was whichever the engine picked, so even the rows that survived do not mean anything in particular. Run the same query on a real workspace twice and the surviving rows can differ.
innerunique exists for a reason: when the left side is a list of keys and the right side holds the detail, deduplicating the left prevents a key listed twice from doubling the result. That is a narrow case. For almost every security question, where the left side is a table of events, the default drops the events the question was about.
The order of the tables matters to the default as well, because only the left side is deduplicated. Put the inventory on the left and the process table on the right, and the result changes completely, which is one of the experiments suggested at the end of this lesson. A join whose answer depends on which table is written first is a join whose behavior was never stated.
The defense is simple and absolute: write kind= on every join. It costs a few characters, it says to the next reader what the join is meant to do, and it removes the one join behavior that drops rows without being asked to. Module 5 gives this a whole lesson, because published queries still rely on the default.
An Inner Join Multiplies
Every left row with every matching right rowAn inner join keeps every left row that has a match and pairs it with every matching right row. With the default replaced by kind=inner, the same join becomes:
DeviceProcessEvents
| join kind=inner DeviceInfo on DeviceId
| count
This time nothing on the left is deduplicated, and the count reflects it:
Count
------
143595
143,595 rows from 9,578 process starts. Now the result is far too large. Each process on an inventoried device matched all 15 of that device's daily snapshots and appeared 15 times, once beside each. Any count, rate or sum computed on this result is inflated 15 times over, and a report built on it would say the estate ran 15 times as many programs as it did.
This is the mirror image of the default's mistake, and it comes from the same place: neither query asked how many rows the right side had per key. The inventory is a table of snapshots, not a table of devices. Joining events to snapshots pairs every event with every snapshot, which is almost never what an analyst means.
The arithmetic is also the check. 143,595 divided by 15 is 9,573, a whole number, which says every inventoried device contributed exactly 15 copies of each of its processes. When a join's result is an exact multiple of a sensible count, it has multiplied; when it is a small fraction of the left side, it has deduplicated. Both are visible from a count before and after.
Multiplication is also expensive. An inner join that returns 15 times the rows does 15 times the work after it: every extend, summarize and sort downstream handles the copies. On a large workspace that is the difference between a quick query and one that runs out of memory, and the engine's error then points at the join rather than at the question.
Module 5's lesson on matching rows across tables starts here, with the rows-per-key prediction, because every other join problem is a variation of it.
One Right Row per Key
Reduce the inventory to the latest snapshot firstThe fix for multiplication is to give the right side one row per key before joining. arg_max(Timestamp, ...) keeps, for each device, the columns named from its latest row, so the inventory becomes one row per device:
DeviceProcessEvents
| join kind=inner (
DeviceInfo
| summarize arg_max(Timestamp, OSBuild, SensorHealthState) by DeviceId
) on DeviceId
| summarize Processes = count(), Devices = dcount(DeviceId)
by OSBuild, SensorHealthState
The join now pairs each process with one inventory row, and the summarize groups by what the inventory added:
OSBuild SensorHealthState Processes Devices
---------- ----------------- --------- -------
10.0.26100 Active 9573 15
9,573 process starts on 15 devices, every one with the same build and a healthy sensor. Each process now appears once, so the count is a count of processes.
The inventory in this lab is uniform, so the enrichment says little; in a real estate the same query splits activity by operating system, build or sensor state, and is how an analyst finds that a vulnerable build is the one running a suspicious program.
The right side was reduced inside parentheses, as a query of its own, before the join used it. Any tabular expression can sit there, and filtering or summarizing the right side before the join is how most joins should be written: the engine matches fewer rows, and the reader sees exactly what the left side is being matched against.
Which inventory row to keep is a decision, not a detail. The latest snapshot describes the device as it is now, which suits most questions.
A question about the device as it was when a process ran needs the snapshot nearest that time, which is a join across time, the subject of Module 5's seventh lesson. Saying which one the query keeps, in the query, is what makes the result checkable.
The count is also 5 short of 9,578. An inner join keeps only processes with a match, so those 5 rows had no inventory row to match. Whether that is right depends on the question. Here the question was about inventoried devices, so they belong out. A question about all process starts would need them back, and the next section shows how to keep them and how to find them.
Rows With No Match
leftouter keeps them, leftanti lists themA left outer join keeps every left row whether or not it has a match, and fills the right side's columns with empty values where it does not. That makes it the safe join for enrichment, because it can add context and never removes anything:
DeviceProcessEvents
| join kind=leftouter (
DeviceInfo
| summarize arg_max(Timestamp, OSBuild) by DeviceId
) on DeviceId
| summarize Processes = count(), NoInventory = countif(isempty(DeviceId1))
All of the process starts come back, with a count of those the inventory could not enrich:
Processes NoInventory
--------- -----------
9578 5
All 9,578 process starts survive, and 5 have no inventory match. The key column from the right side, DeviceId1, is empty in exactly those rows, which is how the query counted them. The total is the left side's total, which is the property that makes leftouter the right default for adding context: the answer to how many processes there were does not change because a lookup was added.
Notice how the empty rows were counted. After a leftouter, the right side's columns exist in every row and are empty where there was no match, so a test on any of them, here the copy of the key the join added as DeviceId1, separates matched from unmatched rows.
Testing a column that can be empty for other reasons would blur that line, which is why the key is the column to test.
A left anti join does the opposite of an inner join: it keeps only the left rows with no match, and none of the right side's columns. It answers a different kind of question, what one table has that another lacks:
DeviceProcessEvents
| join kind=leftanti DeviceInfo on DeviceId
| summarize Processes = count(), Devices = dcount(DeviceId)
It returns the process starts the inventory could not match, and the devices they ran on:
Processes Devices
--------- -------
5 3
5 process starts on 3 devices that are absent from the inventory. That is a finding in its own right. Devices that run processes and report telemetry but have no inventory record are a coverage gap, and the right next step is to find out what they are and why the inventory does not list them, which later modules do.
Three routes reached the same number here: the leftanti count, the empty rows of the leftouter, and the difference between the inner join and the whole table. That agreement is the check.
Choosing the Join Kind
Name the kind, predict the count, check itEvery join on this page was valid KQL, and they returned 225, 143,595, 9,573 and 9,578 rows from the same two tables. The difference between them was entirely the kind of join and the number of rows per key on each side. Choosing the kind is a decision about the question, and it comes down to a short climb:
The ladder puts leftouter first because it is the join that cannot lose rows, and the last rung is the default because it is the one join that should never be used by accident. Most analysts' mistakes with joins are the three below, and each one is caught by counting rows before and after:
Two further kinds are worth knowing by name, though this lesson did not need them. rightouter and rightanti are the mirror images of leftouter and leftanti, keeping or listing the right table's rows; fullouter keeps every row from both sides.
They are rarely needed, because a query can almost always be written with the table whose rows matter on the left, which keeps the meaning of the result easy to state.
The habit that catches all three is to predict the count before running the join. Count the left side, count the right side per key, and say what the join should return. A result that matches is a join you understand; a result that does not is the cheapest bug you will ever find.
What Module 5 builds
Module 5Module 5 builds on this lesson: putting sources side by side, matching rows, the default join's dropped rows, keeping unmatched rows, what one table lacks, adding context, joining across time, and the joins that return the wrong answer.
The habits to carry into it: use union for the same kind of record and join for matching, always write kind=, give the right side one row per key, keep rows with leftouter, and reconcile every join against a count.
Practice
Pick the join that answered the question prepared evidenceThe prepared evidence below is every join this lesson ran, with the rows each returned. The question is how many process starts happened on inventoried devices. Find the join that multiplied rows, the join that answers the question, and the number of process starts the inventory cannot explain. Try each question before opening its answer, and say which count decided it.
The question is narrower than it looks. It asks about process starts, so the answer cannot exceed the process table's row count, and it asks about inventoried devices, so rows with no inventory match belong out. Reading a question for those two limits before looking at any result is how a correct join is picked out from a list of plausible ones.
The two rows at the top, each table alone, are what makes the rest readable. Every join result can be judged against them: more than the left side means multiplication, far fewer means deduplication or a filter, and a join that keeps exactly the left side is one that added columns without changing which rows exist.
Then run the queries on this page and change them. Swap the two tables in the default join so DeviceInfo is on the left and see what the deduplication does then, and change arg_max to arg_min in the one-row join to keep the earliest snapshot instead, then check that the count of processes does not change.
A join's result depends on the kind and on the rows per key on each side. Always write kind=, reduce the right side to one row per key, use leftouter to enrich without losing rows and leftanti to find what is missing, use union for records of the same kind, and count before and after every join.
The next lesson goes inside a single value: fields pulled out of free text, nested data in dynamic columns, and arrays turned into rows.