We've looked at the LEFT and RIGHT JOINs. These can be useful relative to the result set we want, thought RIGHT JOINs are less common in development and may cause confusion (we can invert the tables and perform a LEFT JOIN for the same results). What if we want the result set of both of these combined - in other words, the result set for the LEFT JOIN and also the result set for the RIGHT JOIN? We can achieve what we've seen in past videos by combining these joins with teh FULL OUTER JOIN. In the video, SQL Basics: How To Use A FULL OUTER JOIN and Why, we do exactly this with our test data set.
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.
- In comparing a LEFT JOIN and a RIGHT JOIN, how will the result of the FULL OUTER JOIN differ?
- When we create the FULL OUTER JOIN on Id, what's the result?
- What would be the result if we used the column Letter?
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.
How could this be useful? In reviewing our test data set, we see that the final result shows us where the Ids come with values on the left side and right side while the opposite side has nulls and where the values align. Look at the values 3 and 4: we have matches. However, we don't see this for 1 and 2 (the left Ids return while the right don't because there are no matches on the right). Likewise, the right table with 5 and 6 come with Ids while these are not present in the left table. This means that with a FULL OUTER JOIN we can get non-null values on the left side, right side and where both align. This can be extremely useful in filtering or analysis where we may want values that match and don't match from both tables on either side. While this certainly comes up less than LEFT JOINs, it is worth knowing how to use when you need to use it.
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.