Joining data to make successful queries

In the previous blog post, we have learned how to use string operators to query data. Querying data is one step but using it is the next step. So in this blog post, we will learn how to use the join operator. We will do this by comparing apples and pears.

We can use the join operator to join tables but also let statements, as long as you have two columns that have matching values and are the same data type. The join operator has 9 flavors and uses the innerunique by default.

Although the default join flavor is the innerunique it is not always the best flavor for security purposes. It takes the key and does not look at the values behind this. To demonstrate this we will use the query below.

let Fruit = datatable(number:int, fruit:string) 
[
1, "Apple", 
1, "Pear" 
];
let Preparation = datatable(number:int, fruit:string) 
[
1, "Slices",
1, "Juice" 
];
Fruit
| join kind = innerunique Preparation on number

So the query did a loop through the number column but did not take the fruits. We can create a query to return the pear values by adding the following line.

| where fruit == "Pear"

When we want to display all combinations we will use the flavor inner.

let Fruit = datatable(number:int, fruit:string)
[
1, "Apple",
1, "Pear"
];
let Preparation = datatable(number:int, fruit:string)
[
1, "Slices",
1, "Juice"
];
Fruit
| join kind = inner Preparation on number

So the inner flavors return the values that are combinations. But what if we add another fruit which we don’t want to prepare?

let Fruit = datatable(number:int, fruit:string)
[
1, "Apple",
2, "Pear",
3, "Banana"
];
let Preparation = datatable(number:int, fruit:string)
[
1, "Slices",
1, "Juice",
2, "Juice"
];
Fruit
| join kind = leftouter Preparation on number

And we can do the opposite from the right side with rightouter.

let Fruit = datatable(number:int, fruit:string)  
[
1, "Apple",  
2, "Pear",
];
let Preparation = datatable(number:int, fruit:string)  
[  
1, "Slices", 
2, "Juice",
3, "Dry"  
];
Fruit
| join kind = rightouter Preparation on number

To show all results from both sides we use the fullouter.

let Fruit = datatable(number:int, fruit:string)  
[
1, "Apple",  
2, "Pear",
4, "Banana"  
];
let Preparation = datatable(number:int, fruit:string)  
[  
1, "Slices", 
1, "Juice",
2, "Juice",
3, "Dry"  
];
Fruit
| join kind = fullouter Preparation on number

After inner and outer the next flavor will be semi. We will use leftsemi and rightsemi to show all matches and only the left or right column.

let Fruit = datatable(number:int, fruit:string)  
[
1, "Apple",  
2, "Pear",
4, "Banana"  
];
let Preparation = datatable(number:int, fruit:string)  
[  
1, "Slices", 
1, "Juice",
2, "Juice",
3, "Dry"  
];
Fruit
| join kind = leftsemi Preparation on number

And now for the right side.

let Fruit = datatable(number:int, fruit:string)  
[
1, "Apple",  
2, "Pear",
4, "Banana"  
];
let Preparation = datatable(number:int, fruit:string)  
[  
1, "Slices", 
1, "Juice",
2, "Juice",
3, "Dry"  
];
Fruit
| join kind = rightsemi Preparation on number

We now know all flavors to show us the things we want to see. But what if we don’t want to see items? Then we use the leftanti and rightanti rules, where leftanti means only from the left side and rightanti only from the right side.

let Fruit = datatable(number:int, fruit:string)  
[
1, "Apple",  
2, "Pear",
4, "Banana"  
];
let Preparation = datatable(number:int, fruit:string)  
[  
1, "Slices", 
1, "Juice",
2, "Juice",
3, "Dry"  
];
Fruit
| join kind = leftanti Preparation on number

And for the other table, we use rightanti.

let Fruit = datatable(number:int, fruit:string)  
[
1, "Apple",  
2, "Pear",
4, "Banana"  
];
let Preparation = datatable(number:int, fruit:string)  
[  
1, "Slices", 
1, "Juice",
2, "Juice",
3, "Dry"  
];
Fruit
| join kind = rightanti Preparation on number

During this blog post, we have compared apples and pears and learned the 9 flavors of join operators. We have also learned that the default innerunique flavor doesn’t always return the results we would expect. And that we can further target our joins with the where operator. In the next blog post we will use the join operator to join different alerts.

Thanks for reading and if you have any questions or ideas for a blog post let me know.