KQL skip join if table is empty

Question

I have two tables, and I want to join table1 with table2. But in case table2 is empty, return just table1 without the join.

let table1 = datatable (col1: int, col2: string)
[
    1, "A",
    2, "B",
    3, "C"
];
let table2 = datatable (col1: int, col3: string)
[
    1, "X",
    2, "Y"
    // If table2 is empty, comment out the above two lines
];
let isEmpty = toscalar(table2 | count | project isempty = Count == 0);
table1
| join kind=inner (table2) on col1

For example in this scenario, I want to get

col1	col2	col11	col3
1	A	1	X
2	B	1	Y

But if table2 is empty, get the entire table1:

col1	col2
1	A
2	B
3	C

How can I do that?

Answer 1

I have two tables, and I want to join table1 with table2. But in case table2 is empty, return just table1 without the join.

To achieve your desired output, try the code below, where I used the union operator on a Table1 and an inner join to check whether there is an empty table or not.

let table1 = datatable (col1: int, col2: string)
[
    1, "A",
    2, "B",
    3, "C"
];

let table2 = datatable (col1: int, col3: string)[];

let isEmpty = toscalar(table2 | count | project isempty = Count == 0);

let func1= view(){
table1
};

let func2 = view(){
table1
| join kind=inner (table2) on col1
};

union (func1() | where isEmpty), (func2() | where not(isEmpty))

In the above case as the Table2 is empty, it prints the data present in the Table1.

Output:

Col1    Col2    
  1       A       
  2       B
  3       C

While Table2 is not empty:

let table1 = datatable (col1: int, col2: string)
[
    1, "A",
    2, "B",
    3, "C"
];

let table2 = datatable (col1: int, col3: string)
[
    1,"K"
];

let isEmpty = toscalar(table2 | count | project isempty = Count == 0);

let func1= view(){
table1
};

let func2 = view(){
table1
| join kind=inner (table2) on col1
};

union (func1() | where isEmpty), (func2() | where not(isEmpty))

In this case as the Table2 consists of data, it uses inner join and prints the common data from both the tables as you can see in the below output: Output:

Col1    Col2    Col1    Col3
  1       A       1       K