Let's talk a little bit more about Join specifics and get into some examples. So, there's many different types of joins that are out there and those of you who have practiced SQL before, the Venn diagram slide is coming, I assure you. So, you have an inner join, left join, right join, a full outer join. Now, the best way to think of this is through an example and I have a handy-dandy whiteboard that I'm just going to pull up, and I'm going to quiz you guys on. So, we have in my handwriting here, I hope you can read this, we have stations and then, we have the temperature readings. Now, what the default Join is, it's called an inner Join, so the circle represent all the data that's in that one particular table. So, everything in this first circle is the stations reading, the stations themselves like Wake Island or if it's Australia and the address and latitude longitude. Then on the right-hand side you have the individual temperature readings by hour by day, and again, we're linking them on those identifying fields. So, let's go over the different types of joins. So, an inner join, if you just typed in the word J-O-I-N it'll default to an inner join, represents the intersection of data that's found both in the station's table and in the temperature readings table. So, what that means is there's a reading that corresponds to a temperature station and this is how you can discern if there's data quality issues in your dataset. For example, what if we did a left join, left join means include all the records from the leftmost table, which means the stations and those that match from the right table only where they match, you see just this intersection. Now, what is this other piece that we just introduced in blue here? So, the inner join piece was this red piece where those records actually matched CF stations that have readings. Now, think about what about those pieces in blue that don't have any data that matches from the temperature reading's table? What does it actually mean talking it through? So, if you said these are brand new stations, potentially, if we just stood up a new weather station in Antarctica or somewhere around the world that has not had any readings, recordings attached to it yet, that means you have a record in the station's table that has a proper latitude longitude but there are no readings associated with that station yet. So, that's the additional data that you get above and beyond an inner join. All right. So now, to really test your knowledge. What would a right joins, right joins are commonly not used because a right join is the same thing as inverting the two tables wishing them and then just using a left join, but what would something like this include? So, we already have everything from the inner matching piece, meaning all of the stations that have readings are going to be included. I'll talk through this with you. Anything previously that we saw so brand new station readings, would they be included or not in this red join? The answer is they would not be included because again, we're not doing a left join, we're doing a right join. But what would be included? This is where it gets a little bit tricky. So, you have readings data that doesn't necessarily correspond to a station. So, this is definitely thinking through the example that we have, a huge anomaly. So, if you have a temperature reading, say a sensor or something like that, that doesn't correspond or doesn't belong to a station, that's a big red flag for us and so we wouldn't normally expect that. Then last but not least, you can have a full outer join, which means, give me all the data from the left table and all the data from the right table regardless of whether or not they have matches. So, what that means is you have stations that could be brand new that have no readings and you can have anomalous sensors that don't correspond to a station reading. Inside of your actual SQL results, when you have results from one table, but not the other, you'll still have the columns from the other table returned as as an example. You could have a brand new station that was just opened up and for those daily temperature readings, those minimum and maximum temperatures, you would actually just have null values for all of those to this sore nulls really come into play as valid values, and it's very common to use these the Venn diagrams to think through and project out how you're actually going to be joining your data. Okay and that's exactly what we talked about here. So, inner join is that intersection p so that Venn diagram, left join is all the rows from that left side where and where they match with the right side, right join again it's never seen used because you're just switching the order in SQL literally of your table name and an outer join is just a full outer join that is just a way of returning all rows from all tables. So, there's actually two major types of different joins; inner joins versus outer join. A left is considered an outer join so it's actually left outer join, right outer join and full outer join. Again if you just typed in "join" into SQL, that will default to an inner join, so only when you have matching records. As a review, that's exactly what we just covered with our Venn diagram example.
