SQL Basics: INNER JOINs vs LEFT JOINs
For people who are new to SQL, these two JOIN types can often be confusing. In the below videos and discussion we compare and contrast the two using the same data set and noting the results.
INNER JOINs match data on their condition. Suppose that we had a table called Insects, which stored exclusively detailed insect data, and we had a table called AllAnimals, which general information of every organism. Assuming that we had an identifier that could link these tables, if we joined these tables with an INNER JOIN, then the only results that would come back would be insects. This is because these would be the only data values that "matched" between the tables; detailed data about canines would not return because while canines would be in the AllAnimals table, they would not be found in the Insects table. This means that INNER JOINs intentionally exclude data that do not match based on the condition of the join. By contrast, if we wanted to return records that matches, but also didn't match, we wouldn't want to use an INNER JOIN.
One key to note with INNER JOINs is that we specify a condition. If we specify the wrong condition, we may get no records or erroneous records. The condition in an INNER JOIN acts as a filter, just like the matching act as one as well. In the video example that we see with the two tables, consider how using a different column in the join would have affected the result.
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.