T-SQL: How To Write A Left Join and Why

Previously, we looked at INNER JOINs and where we may consider using them in development — a quick example might be if we have a set of data that shares a relationship with another data set and we want to return all the data from both data sets where we have matching data. There may be situations where we want to return data that also don’t match, or we may want to use the fact that the data don’t match between data sets as a filter of sorts. There are a variety of techniques to solve this and LEFT JOINs provide us with one way to accomplish this. In the video, SQL Basics: How To Use A LEFT JOIN and Why, we look at several examples of using a LEFT JOIN and why we might consider using this, as opposed to what we learned in an early week with an INNER JOIN.

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 first our example, what do we LEFT JOIN on? Suppose that we use a different column — how might the result set that’s returned in the first example differ?
• In our second example, we flip the tables that are being joined — how does this affect the result set (if applicable)?
• Based on the results from the first two examples, how are these examples showing the LEFT in the LEFT JOIN — consider what’s happening with the results from both tables being used.
• If records match, do we get these results with a LEFT JOIN? Considering this question, how does LEFT JOIN differ from INNER JOIN?

When it comes to applying LEFT JOINs to a problem (real world use), we can see that LEFT JOINs make it easy to determine based on linking a column or set of columns what data match between two tables and what data don’t match. Suppose that we were comparing genetics of two population groups and we wanted to see the genetic overlap between these populations, but also the differences between these groups. We could accomplish this with LEFT JOINs (not the only way to accomplish this in SQL, but one way). In the world of databases, this comes up regularly — we have a source data set and we want to compare with a destination data set. Often this will involve inserting data that doesn’t exist in the destination from the source or updating information in the destination that already exists in the source.

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