azurekqlazure-data-explorerkusto-explorer

How to use table alias in KQL query


I want to select some columns in multiple temporary table. So, I need to select columns with table alias in KQL.

Example in ANSI SQL:

Select a.col1,b.col2 from a inner join b on a.id = b.id

Example in KQL:

let TableA = external_table('table1')
| take 100
let TableB = external_table('table2')
| take 100 
TableA
| join kind=inner (TableB) on id
| project TableA.id,TableB.col2

I encountered an error as shown below: enter image description here

How can I change the KQL query to resolve this error. Selecting multiple columns from multiple tables in KQL.


Solution

  • Have you tried this:

    EDIT 1

    let TableA = external_table('table1')
      | take 100
      | project TableAId = id, TableACol2 = col2;
    let TableB = external_table('table2')
      | take 100
      | project TableBId = id, TableBCol2 = col2;
    TableA
      | join kind=inner (TableB) on $left.TableAId == $right.TableBId
      | project TableAId, TableBId, TableACol2, TableBCol2
    

    Here you are renaming the Id-Columns explicitly, to reuse them in the resulting JOIN.