T-SQL: How To Write A RIGHT JOIN and Why You Should Be Aware of Them

We've previously looked at the LEFT JOIN and we can't invert the LEFT JOIN to do a RIGHT JOIN (though I would caution on doing this because it begins to violate using a consistent standard in an environment). We should know that this functionality exists, as you may run into it, even if it's not what I would recommend. The key is to understand how this will impact our results relative to the LEFT JOIN and if we're in control of the architecture, that we may want to re-write it. In the video, SQL Basics: How To Use A RIGHT JOIN and Why, we look at several examples of using a RIGHT JOIN and why we might consider using this, as opposed to what we learned in an earlier lesson involving LEFT JOINs.

Some questions that are answered in the video:

• Note the tables that we're using and what values are identical based on the column names and what values differ based on the column names. We'll be using these tables throughout these videos. As a note on aliasing tables - the first table always gets a "t1" while the second table always gets a "t2" and so on and so forth (the 5th table would get a "t5"). Finally, consider that some SQL languages may not support the JOINs that we discuss.
• How do the results of the RIGHT JOIN differ from what we learned previously with the results of the LEFT JOIN?
• Consider that using LEFT and RIGHT joins together may create confusion when developers try to conceptualize what the query is running. While it's useful to know how to write a RIGHT JOIN, I would advise to using a standard with JOIN types like these (almost always LEFT) so that developers can easily understand what's being queried. Combining types will create confusion. This being said, it's important to know how to write these, as consultants will often run into these in environments that may not have standard practices - which are popular environments for consultants. Often these RIGHT JOINs indicate that there's a lack of standards, which is contributing to the problems.
• If we were consultants debugging code and we came across this being used, how would we write the queries with LEFT JOINs instead?

For mastering data transformation from one form to another form, check out the highest-rated Automating ETL course on Udemy. For a coupon to the course, check out the trailer video on the channel SQL In Six Minutes.

As noted, to avoid confusion, I would avoid using these unless your environmental standard allows for these or unless there is a specific situation where these are appropriate (which should almost always be "never" since we can invert the solution with a LEFT JOIN). You should know that these exist and you will see them every now and then (especially on consulting), but be aware that 99% of the rare times they're used, you've witnessed an environment not using consistent SQL standards. When people ask why following a standard is important, it's worth remembering that developers spend much more time debugging code than writing code in most situations. If we're spending most of our time writing JOINs from left over right, to invert that, may cause confusion for developers because they have to stop and consider how that impacts results. Compare the results of a LEFT JOIN with a RIGHT JOIN using our test data set that we've been using. Now, imagine writing POCs and unit tests for an inverted case - suddenly we have to flip everything around. This is why following a standard can make our life easier - we have much less to troubleshoot and we can conceptualize our troubleshooting one way. Consider that there are situations where we have 10-30 joins and in those cases, we really don't want mixing and matching of join types that differ from left to right.

For more SQL lessons, you can continue to watch the SQL Basics series.

Are you looking for tech consultants that can assist with design and development? From building custom applications to working with existing infrastructure that's causing you trouble, we can get you connected to consultants who can assist. You can contact for assistance.