Forum Discussion
Best Merge Queries Join Kind for Best Performance on below scenario
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.
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!!
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 ?