How do I decide , When to use Right Join/ Left Join or Inner Join or How to determine which table is on which side?

Question

Hi , I know the usage of joins but sometimes i came across such a situation when i was not able to decide which join will be suitable a left or right. Please show me some light.

Here is the query where i am stucked

SELECT      count(ImageId) as [IndividualRemaining],
                        userMaster.empName AS ID#, 
                        CONVERT(DATETIME, folderDetails.folderName, 101) AS FolderDate, 
                        batchDetails.batchName AS Batch#, 
                        Client=@ClientName,
                        TotalInloaded = IsNull(@TotalInloaded,0),
                        PendingUnassigned = @PendingUnassigned,
                        InloadedAssigned =  IsNull(@TotalAssigned,0),
                        TotalProcessed = @TotalProcessed,
                        Remaining = @Remaining
            FROM         
                        batchDetails 
                                        Left JOIN   folderDetails ON batchDetails.folderId = folderDetails.folderId 
                                        Left JOIN   imageDetails ON batchDetails.batchId = imageDetails.batchId 
                                        Left JOIN   userMaster ON imageDetails.assignedToUser = userMaster.userId

            WHERE     folderDetails.ClientId =@ClientID and verifyflag='n'
                      and folderDetails.FolderName IN (SELECT convert(VARCHAR,Value) FROM dbo.Split(@Output,',')) 
                      and userMaster.empName <> 'unused'

            GROUP BY userMaster.empName, folderDetails.folderName, batchDetails.batchName

            Order BY folderDetails.Foldername asc

Answer 1

yes its depends on situation in you are

Why Use SQL JOIN?

Answer : Use the SQL JOIN whenever multiple tables must be accessed through a SQL SELECT statement and no results should be returned if there is not a match between the JOINed tables.

read this orignal article on cp will help you lot : Visual Representation of SQL Joins

Also check this post : SQL SERVER – Better Performance – LEFT JOIN or NOT IN?

Find orignal one at : Difference between JOIN and OUTER JOIN in MySQL

Answer 2

I think what you're looking for is to do a LEFT JOIN starting from the main-table to return all records from the main table regardless if they have valid data in the joined ones (as indicated by the top left 2 circles in the graphic)

JOIN's happen in succession, so if you have 4 tables to join, and you always want all the records from your main table, you need to continue LEFT JOIN throughout, for example:

SELECT * FROM main_table
LEFT JOIN sub_table ON main_table.ID = sub_table.main_table_ID
LEFT JOIN sub_sub_table on main_table.ID = sub_sub_table.main_table_ID

If you INNER JOIN the sub_sub_table, it will immediately shrink your result set down even if you did a LEFT JOIN on the sub_table.

Remember, when doing LEFT JOIN, you need to account for NULL values being returned. Because if no record can be joined with the main_table, a LEFT JOIN forces that field to appear regardless and will contain a NULL. INNER JOIN will obviously just "throw away" the row instead because there's no valid link between the two (no corresponding record based on the ID's you've joined)

However, you mention you have a where statement that filters out the rows you're looking for, so your question on the JOIN's are null & void because that is not your real problem. (This is if I understand your comments correctly)