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.