When storing data, one technique to storing data is linking information through identification of the data. This is the dominant way that SQL engines operate 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 with an example of where we’d use it.
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.
- In first our example, what do we INNER JOIN on? How might that differ if we use another column? Why?
- In our second example, what do we INNER JOIN on? What’s the result compared with the first result? Why did this occur?
- What is the bonus note in the video for people who watch the full video? Why is this important?
- What is a distinct value?
One quick note about inner joins is that if one table has multiples of the join condition, the result will be multiples because of how it functions — it returns matches. In our example, if Table2 had 2 3s for Id, then we would have 2 3s as a result because one of the tables had multiples.
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).
Top comments (0)