Weekly Tech Lesson: Using INNER JOINs In SQL

avatar

When storing data, one technique to storing data is linking information through identification of the data. Every data value that we store can be linked to other data values that are related - an example would be a home detail being linked to home sales history for a particular home that we have a unique identifier for. This is the dominant way that SQL engines operate when approaching data storage and within SQL engines comes join functionality - one of which is the INNER JOIN. In the above video, we look at how to write an INNER JOIN using the SQL engine of SQL Server with an example of where we'd use INNER JOINs in a business context. Note the table structure in the example, as we'll be using the same table structure throughout most tutorials.

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 match, but also didn't match, we wouldn't want to use an INNER JOIN.


One key to note with INNER JOINs is that we must specify a condition on the join. If we specify the wrong condition, we may get no records or erroneous records. The condition in an INNER JOIN acts as a filter since it only returns matching records. In the video example that we see this with the two tables and consider how using a different column in the join would have affected the result. I recommend that you take the same data in the two tables and test joining other columns with an INNER JOIN, then comparing the result.

A practical example of an INNER JOIN in business might be if we had two tables, one of which stored a list of homes available for sale in an area with a unique identifier for each of the homes and another table stored the pricing history for all the unique identifiers of the homes. If we wanted to get the pricing history for homes, we would join the two tables on the unique identifier. This example also illustrates where we would use foreign key relationships, as we can't have a price of a home that doesn't exist - thus a home would have to first exist before we could store its pricing history. In this example, there could be a one-to-many relationship, as one home could have multiple prices listed in its history (very common, unless a home is new and hasn't been sold).


Source: SQL Basics: How To Use An INNER JOIN and Why. Different SQL languages may slightly differ in their syntax, as we see an example of T-SQL in the above INNER JOIN. The functionality of what we're doing always stays the same, while the syntax may only slightly differ.



0
0
0.000
0 comments