Amit Ranjan Amit Ranjan - 5 days ago 6
MySQL Question

How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?

I know the usage of joins, but sometimes I come across such a situation when I am not able to decide which join will be suitable, a left or right.

Here is the query where I am stuck.

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

Yes, it depends on the situation you are in.

Why use SQL JOIN?

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

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

alt text

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

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

Comments