SQL Tutorial: LEFT JOIN Basics and When To Use Them

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 to filter data in or out of our returned set. There are a variety of techniques to solve this and LEFT JOINs provide us with one way to accomplish these tasks. 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.

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. We'll note that when we LEFT JOIN between the two tables, that we get some records that match and are returned, but we get some that don't match and return with NULLs. This is expected behavior, as a LEFT JOIN will return rows that match and don't on the condition. We have 2 Ids that match, but 2 don't, thus those 2 that don't return. Notice what occurs when we flip the example - this happens because of the "initial" table in the JOIN. Why may we want some records to not match? We may feel that INNER JOIN makes more sense, but there are situations where we want records to come back that match and don't because we may apply a filter on the records that do/do not match (we can compare differences).

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.

When it comes to these SQL Basics, most SQL languages support these operations, though there may be alternative ways to write them. For more SQL lessons, you can continue to watch the SQL Basics series.