INNER, LEFT or RIGHT JOIN to get columns values

Question

I have 4 tables in SQL Server 2012. This is my diagram:

I have this query:

SELECT 
    pc.Product_ID, d.Dept_ID, c.Category_ID, sc.SubCategory_ID 
FROM 
    dbo.ProductConfiguration pc
INNER JOIN 
    dbo.SubCategory sc ON sc.SubCategory_ID = pc.SubCategory_ID
INNER JOIN 
    dbo.Category c ON c.Category_ID = sc.Category_ID
INNER JOIN 
    dbo.Department d ON d.Dept_ID = c.Dept_ID
WHERE 
    pc.Product_ID = 459218

What is the best way, (INNER, LEFT, RIGHT) to get columns values? I need be careful with performance

Thanks a lot

They have different meanings; which one to choose depends on how the data is located and your need. Look at this link https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins — Fact, Mar 16 '18 at 00:06
[what-is-the-difference-between-inner-join-and-outer-join](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) OR [Whats the difference between inner join, left join, right join, and full join](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) -- Between these two posts, you have over 2.8 million views. This question appears to be too broad or should be closed as duplicate. — Brien Foss, Mar 16 '18 at 00:10

score 0 · Answer 1 · answered Mar 16 '18 at 00:05

This has nothing to do with performance.

I suppose every product belongs to a subcategory which belongs to a category which belongs to a department. So you inner join all the tables; this is the normal thing to do.

If one of the entities were not mandatory, but optional, say there are categories that don't belong to a department ¹, but you still wanted to show the row, then you'd outer join the table. You'd use a left outer join on departments. We never use right outer joins, because these are supposed to be harder to read, and they do essentially the same as left joins.

^{1) i.e. category.dept_id coud be null}

score 0 · Answer 2 · answered Mar 16 '18 at 05:54

0

your query best variant is LEFT JOIN, from its do not loses your information

answered Mar 16 '18 at 05:54

score 0 · Answer 3 · answered Mar 16 '18 at 07:20

0

I would recommend to you, use execution plans when you need to update the performance. using execution plan you can understand the behaviors. it will grate help for future. Thank You!

answered Mar 16 '18 at 07:20

Tharindu Madushanka

100
1
10

Thanks for the recomendation – ericardezp Mar 16 '18 at 15:49

INNER, LEFT or RIGHT JOIN to get columns values

3 Answers3