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.
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. A big difference with the RIGHT JOIN and LEFT JOIN is one joins from the left, while the other joins from the right. This can be a big concern in development because mixing join types may cause confusion for developers. We'll notice the results in the video and how they differ from what we learned with LEFT JOINs. Imagine trying to troubleshoot a query when you have six LEFT JOINs and one RIGHT JOIN. In that example, the RIGHT JOIN makes little sense, as we could re-write it with a LEFT JOIN instead and have seven LEFT JOINs. This is why we should be aware of this functionality because we will sometimes consult in an environment where developers have chosen to suddenly switch join types. Unless RIGHT JOINs are the standard (in which case, we'd find no LEFT), stick to using the standard, as it will be more intuitive. I caution developers that debugging makes a huge portion of coding over coding itself, so writing things in an easy way to debug is key.
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.
When it comes to these SQL Basics, most SQL languages support these operations, though in this case, I would avoid using a RIGHT JOIN and stick with using LEFT JOINs. We may still run into these, so it's good to know what they mean. For more SQL lessons, you can continue to watch the SQL Basics series.
