ADX Kusto Self Join with a combination of equality and inequality

Question

ADX Kusto Self Join with a combination of equality and inequality

databricksuser-5173 0

Hi,

How do we generate Kusto query to achieve a self-join query, where one column matches but the other doesn't?

For example:

myTable

| where myColumnSubset == "mySubGroup"

| join kind=inner (

    myTable

    | where myColumnSubset == "mySubGroup"

    | project myColumnOne, myColumnTwo, myColumnThree

)

on $left.myColumnOne == $right.myColumnOne

The above query works.

If I add below further inequality criterion, it fails

$left.myColumnTwo != $right.myColumnTwo

Thank you

Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-15T02:34:37.5533333+00:00
@databricksuser-5173

Using an equality condition in the join on clause works as expected. However, when you try to introduce an inequality condition like $left.myColumnTwo != $right.myColumnTwo directly in the on clause, the query fails. This is expected behavior in Kusto, as the join on clause supports only equality comparisons.

Reference: https://learn.microsoft.com/en-us/kusto/query/join-operator?view=microsoft-fabric

To apply inequality conditions, you can use a where clause after the join operation. Here's how you can modify your query:

myTable | where myColumnSubset == "mySubGroup" | join kind=inner ( myTable | where myColumnSubset == "mySubGroup" | project myColumnOne, myColumnTwo, myColumnThree ) on $left.myColumnOne == $right.myColumnOne | where $left.myColumnTwo != $right.myColumnTwo

This approach ensures that the join is performed on valid equality conditions, and the inequality filter is applied afterward.

Note: Equality joins are optimized for performance in KQL. The system can leverage indexing, hash joins, or other efficient techniques to quickly match rows on common values. When you use a non-equality condition, it complicates the ability to efficiently match rows because the engine can no longer rely on the common data structures used for equality joins.

Non-equality joins (e.g., $left.State != $right.State) require checking all possible combinations of values between the two datasets, which makes it significantly more resource-intensive. This could potentially lead to performance degradation, especially on large datasets.

For more details refer: https://learn.microsoft.com/en-us/kusto/query/join-operator?view=microsoft-fabric

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
databricksuser-5173 0 Reputation points

2025-04-15T05:45:10.14+00:00

Thank you for the response.

The "where" clause was already tried, with no luck.

| where $left.myColumnTwo != $right.myColumnTwo

The above where clause throws the error "failed to resolve the expression $left.myColumnTwo" So, I even tried to expose the columns in the outer and inner queries using "project" clause and even tried table aliases to associate the columns explicitly. None of these seem to be supported in kusto.
Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-15T18:36:00.82+00:00
@databricksuser-5173

I tried reproducing the scenario from my end, please find the below screenshots:

This is by design, as the Kusto engine optimizes joins using hash-based equality logic. To work around this, the recommended approach is to perform the equality join first and then apply any inequality filters in a subsequent where clause.

To further validate this behavior, I ran a working repro using the built-in StormEvents sample data. Here’s a complete working example that performs a self-join on an equality column (EventType), and then applies an inequality condition (State != State):

StormEvents | where State != "FLORIDA" // Filter out FLORIDA | extend leftState = State // Rename for clarity | join kind=inner ( StormEvents | where State != "FLORIDA" | extend rightState = State | project rightState, EventType, DamageProperty ) on $left.EventType == $right.EventType // Equality join | where leftState != rightState // Apply inequality condition | summarize event_count = count() by leftState | where event_count > 1 | project State = leftState, event_count

Alternate approach:

Could you please try the below approach:

myTable | where myColumnSubset == "mySubGroup" | join kind=inner ( myTable | where myColumnSubset == "mySubGroup" | project myColumnOne, myColumnTwo, myColumnThree ) on $left.myColumnOne == $right.myColumnOne | where myColumnTwo != myColumnTwo

I hope this information helps!
Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-16T06:12:14.8733333+00:00

@databricksuser-5173We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-17T01:06:59.42+00:00

@databricksuser-5173

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

Your answer

Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-15T02:34:37.5533333+00:00

@databricksuser-5173

Using an equality condition in the join on clause works as expected. However, when you try to introduce an inequality condition like $left.myColumnTwo != $right.myColumnTwo directly in the on clause, the query fails. This is expected behavior in Kusto, as the join on clause supports only equality comparisons.

Reference: https://learn.microsoft.com/en-us/kusto/query/join-operator?view=microsoft-fabric

To apply inequality conditions, you can use a where clause after the join operation. Here's how you can modify your query:

myTable | where myColumnSubset == "mySubGroup" | join kind=inner ( myTable | where myColumnSubset == "mySubGroup" | project myColumnOne, myColumnTwo, myColumnThree ) on $left.myColumnOne == $right.myColumnOne | where $left.myColumnTwo != $right.myColumnTwo

This approach ensures that the join is performed on valid equality conditions, and the inequality filter is applied afterward.

Note: Equality joins are optimized for performance in KQL. The system can leverage indexing, hash joins, or other efficient techniques to quickly match rows on common values. When you use a non-equality condition, it complicates the ability to efficiently match rows because the engine can no longer rely on the common data structures used for equality joins.

Non-equality joins (e.g., $left.State != $right.State) require checking all possible combinations of values between the two datasets, which makes it significantly more resource-intensive. This could potentially lead to performance degradation, especially on large datasets.

For more details refer: https://learn.microsoft.com/en-us/kusto/query/join-operator?view=microsoft-fabric

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
databricksuser-5173 0 Reputation points

2025-04-15T05:45:10.14+00:00

Thank you for the response.

The "where" clause was already tried, with no luck.

| where $left.myColumnTwo != $right.myColumnTwo

The above where clause throws the error "failed to resolve the expression $left.myColumnTwo" So, I even tried to expose the columns in the outer and inner queries using "project" clause and even tried table aliases to associate the columns explicitly. None of these seem to be supported in kusto.
Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-15T18:36:00.82+00:00

@databricksuser-5173

I tried reproducing the scenario from my end, please find the below screenshots:

This is by design, as the Kusto engine optimizes joins using hash-based equality logic. To work around this, the recommended approach is to perform the equality join first and then apply any inequality filters in a subsequent where clause.

To further validate this behavior, I ran a working repro using the built-in StormEvents sample data. Here’s a complete working example that performs a self-join on an equality column (EventType), and then applies an inequality condition (State != State):

StormEvents | where State != "FLORIDA" // Filter out FLORIDA | extend leftState = State // Rename for clarity | join kind=inner ( StormEvents | where State != "FLORIDA" | extend rightState = State | project rightState, EventType, DamageProperty ) on $left.EventType == $right.EventType // Equality join | where leftState != rightState // Apply inequality condition | summarize event_count = count() by leftState | where event_count > 1 | project State = leftState, event_count

Alternate approach:

Could you please try the below approach:

myTable | where myColumnSubset == "mySubGroup" | join kind=inner ( myTable | where myColumnSubset == "mySubGroup" | project myColumnOne, myColumnTwo, myColumnThree ) on $left.myColumnOne == $right.myColumnOne | where myColumnTwo != myColumnTwo

I hope this information helps!
Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-16T06:12:14.8733333+00:00

@databricksuser-5173We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ganesh Gurram 6,280 Reputation points Microsoft External Staff

2025-04-17T01:06:59.42+00:00

@databricksuser-5173

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

Share via

ADX Kusto Self Join with a combination of equality and inequality

Your answer