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 video, SQL Basics: How To Use An INNER JOIN and Why, 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.
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.
A practical example of an INNER JOIN would 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).
