Forum Discussion
Community ChampionDefault joins work differently in KQL vs. SQL
It works similar like SQL's default join type (which is inner join) if I explicitly add kind=inner to the join in KQL.
let varStockMarketLast10 = StockMarketRaw | project ['time'], symbol, bidPrice, bidSize, askPrice, askSize, volume | order by ['time'] | take 10; varStockMarketLast10; let varDimStockMarket = StockMarketRaw | distinct symbol, sector, securityType; varDimStockMarket; varStockMarketLast10 | join kind=inner varDimStockMarket on symbol; varDimStockMarket | join kind=inner varStockMarketLast10 on symbol;So the default join kind in KQL is not an inner join, whereas in SQL, the default join kind is inner join.
The default join kind in KQL is:
- innerunique (default)
Inner join with left side deduplication
Schema: All columns from both tables, including the matching keys
Rows: All deduplicated rows from the left table that match rows from the right tableTo be honest, I find the results provided by KQL's default join kind confusing. What is the practical use case for those results? (Ref. step 3 in the original post).
What is the benefit of KQL's default join kind (innerunique)?
I think I will always need to specify join kind in KQL. (Or, be careful about which table is on the right side or left side of the join, when using the default join.)
In SQL, by contrast, it can many times make sense to use the default join kind (inner join), and the left/right position won't affect the number of rows returned.
Still, it is always a good practice to specify join kind explicitly also in SQL, for readability.
Hi frithjof_v,
I agree with your insights.
Quote from Microsoft:
By default, the innerunique join flavor is used if the kind parameter isn't specified.
This default implementation is useful in log/trace analysis scenarios, where you aim to correlate two events based on a shared correlation ID.
It allows you to retrieve all instances of the phenomenon while disregarding duplicate trace records that contribute to the correlation.
More information for your reference:innerunique join - Kusto | Microsoft Learn
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionIt works similar like SQL's default join type (which is inner join) if I explicitly add kind=inner to the join in KQL.
let varStockMarketLast10 =
StockMarketRaw
| project ['time'], symbol, bidPrice, bidSize, askPrice, askSize, volume
| order by ['time']
| take 10;
varStockMarketLast10;
let varDimStockMarket =
StockMarketRaw
| distinct symbol, sector, securityType;
varDimStockMarket;
varStockMarketLast10
| join kind=inner varDimStockMarket on symbol;
varDimStockMarket
| join kind=inner varStockMarketLast10 on symbol;
So the default join kind in KQL is not an inner join, whereas in SQL, the default join kind is inner join.
The default join kind in KQL is:
- innerunique (default)
Inner join with left side deduplication
Schema: All columns from both tables, including the matching keys
Rows: All deduplicated rows from the left table that match rows from the right table
To be honest, I find the results provided by KQL's default join kind confusing. What is the practical use case for those results? (Ref. step 3 in the original post).
What is the benefit of KQL's default join kind (innerunique)?
I think I will always need to specify join kind in KQL. (Or, be careful about which table is on the right side or left side of the join, when using the default join.)
In SQL, by contrast, it can many times make sense to use the default join kind (inner join), and the left/right position won't affect the number of rows returned.
Still, it is always a good practice to specify join kind explicitly also in SQL, for readability.
- frithjof_v
After further reading, it seems that the reason for this different behaviour is performance optimization in KQL's default join type (inneruniqe).
Bottom line: it's important to know that the default join type in KQL is different than the default join type in SQL.
When using the default join in KQL, it seems to make sense to have the Dimension table on the left side, and the Fact table on the right side.
This returns all the matching rows (assuming a 1:many relationship between dim:fact).
It seems to generally be a good practice in KQL to have the Dim table on the left side of the join, and the fact table on the right side of the join.
Pseudo-code:
dimTable | join factTable on keyColumn;I am new to KQL, and curious what others think.
So - what do you think?
Does this align with your understanding?Hi frithjof_v,
I agree with your insights.
Quote from Microsoft:
By default, the innerunique join flavor is used if the kind parameter isn't specified.
This default implementation is useful in log/trace analysis scenarios, where you aim to correlate two events based on a shared correlation ID.
It allows you to retrieve all instances of the phenomenon while disregarding duplicate trace records that contribute to the correlation.
More information for your reference:innerunique join - Kusto | Microsoft Learn
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.