Best Merge Queries Join Kind for Best Performance on below scenario

[vidhyasagar]

In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.

1) Every Product has a ProductSubCategory.

2) Not every ProductsubCategory has a parent ProductCategory.

You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query. How should you merge the tables?

We obviously think 

1) Inner Join Product with ProductSubCategory 

2) Left Join ProductSubCategory with ProductCategory 

But i saw this video explaining about best perfomant Merge Queries Join Kind for first question is Left Join
 Merge Queries Scenario 

which is correct answer , please help with correct option

[Anonymous]

Hi,

Thanks for the solution @shafiz_p  provided, and i want to offer some more information for user to refer to.

hello @vidhyasagar , If you want to display all the main categories, subcategories and products in the same table, although there are some subcategories that do not have a main category, then you can consider using a left join to connect the main category and subcategories, if you only need to show the relevant main categories, subcategories and products, then you can consider using inner joins, you can refer to the following link about them.

www.sqlshack.com

Inner join - Power Query | Microsoft Learn

Left outer join - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vidhyasagar]

Thanks for your time , but in the video i provided , they are mentioning inner join looks for matching rows in both table (from left table to right table once and from right table to left table once) , while left join will look for matching rows from left table to right table once only, using query diagnostics the inner join is taking more time than left join , so best performant is left join ?

[Anonymous · Accepted solution]

Hi @vidhyasagar 

Thanks for your quick reply, as you mentioned, the left join will return the results that match the left table, it seachs the related value from the right table, you can refer to the following picture.

The left table will return all value and the right table only return the related value, so  most of its time is spent querying the right table,  but fot the inner join, you can refer to the following picture.

It needs to query the data that meets the conditions in both tables, so it will query both tables.

So comare them, the left join will be better than inner join, especially if you have a large amount of data, the left join performance is higher than the inner join.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shafiz_p]

Hi @vidhyasagar  You want to merge these three queries into a single query while ensuring optimal performance. Here’s the recommended approach:

1. Left Join the Product table with the ProductSubCategory table using the common key. This ensures that all products are included, even if they don’t have a corresponding subcategory.

2. Left Join the resulting merged table (which includes both products and their associated subcategories) with the ProductCategory table using the common key. Again, this preserves all records, even if some subcategories don’t have a parent category.

The rationale behind using Left Joins for both steps is to avoid excluding any data. Since not every subcategory has a parent category, using Inner Joins could potentially filter out relevant records.

This ensures that you capture all relevant data while maintaining performance.

If I answered your question, please mark it as a solution!!

[vidhyasagar]

Thanks for your time , but in the video i provided , they are mentioning inner join looks for matching rows in both table (from left table to right table once and from right table to left table once) , while left join will look for matching rows from left table to right table once only, using query diagnostics the inner join is taking more time than left join , so best performant is left join ?