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

Question

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

A RIGHT join is the same as a LEFT join except with the order of the tables switched. FROM TableA LEFT JOIN TableB == FROM TableB RIGHT JOIN TableA (although if you use SELECT *, the order of the columns will be different since it emits the columns by table order) — GalacticCowboy, Jul 22 '10 at 17:08
possible duplicate of [What's the difference between (INNER JOIN,LEFT JOIN,RIGHT JOIN,FULL JOIN) in Mysql](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-full-join-in-mys) — JonH, Feb 09 '12 at 16:33
@JonH : See the posted date of the two. If its dupe, I definitely got a time machine or i overflooded the StackOverflow. — , Feb 10 '12 at 09:58
@AmitRanjan Date doesnt matter, content does, its the same content, read the answers. — JonH, Feb 10 '12 at 13:14
http://stackoverflow.com/questions/3183669/difference-between-join-and-outer-join-in-mysql/3183687#3183687.. see it... what you will , answers are also duped. LOLZ.... — , Feb 13 '12 at 11:12
@AmitRanjan yea I agree with you, the other question is the duplicate... if one of them should be closed, it is that other question. — dev_feed, Mar 18 '14 at 15:08

score 131 · Accepted Answer · edited May 23 '17 at 12:10

131

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.

edited May 23 '17 at 12:10

Community

1
1

answered Jul 22 '10 at 11:08

Pranay Rana

175,020
35
237
263

What in case of multiple tables? How do we handle joins? – Jul 22 '10 at 11:16
well its depends on codition you can also use subquery to gain performance over joints but i say its depends on the rows in your table – Pranay Rana Jul 22 '10 at 11:19
yes, i am facing the said situation, joins a'int returning me the rows as a where condition is filtering records.. – Jul 22 '10 at 11:23
On the left second from the top image JOIN shows incorrect diagram. For achieving such result, showed on diagram, we have to set WHERE B.key IS NOT NULL, (not! WHERE B.key IS NULL) sqlfiddle.com/#!9/b25f0b/13 – 1nstinct Jul 15 '15 at 07:44

WonderWorker · Answer 2 · 2016-11-30T16:43:05.517

16

In two sets:

Use a full outer join when you want all the results from both sets.
Use an inner join when you want only the results that appear in both sets.
Use a left outer join when you want all the results from set a, but if set b has data relevant to some of set a's records, then you also want to use that data in the same query too.

Please refer to the following image:

edited Nov 30 '16 at 16:43

answered Mar 31 '14 at 06:33

WonderWorker

8,539
4
63
74

score 7 · Answer 3 · edited Jul 22 '10 at 16:39

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)

I forgot to add, start from the table you want to get data FROM (your main table could be anything) and then either INNER JOIN or LEFT JOIN. It's less complicated that way instead of keeping track of RIGHT JOIN or OUTER JOIN statements — AcidRaZor, Jul 22 '10 at 12:17
actually , i need to generate report. And there are some flag fields that set to Y after there processing. My queries work if i set flag ='n'. But there should be condition if manager wants to view processed data whose flag is set to 'y'. I am updating my question with query,, — , Jul 23 '10 at 03:26

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

3 Answers3

Linked