OK, so let's work on JOINs. How do we link across multiple tables? So conceptually, a JOIN combines data from separate tables into one table. Now technically, a UNION will do that too because it will consolidate that output, but JOINs, you can think about doing it horizontally. So you could actually add in more fields, things like the charity name for example in your IRS example, or the station name in this weather recording example. And this is your JOIN syntax. So you are selecting the fields that you want from both tables. You'll notice that a. b. when you get into that in just a second. From, and you see the UNION wildcard here, on all of that historical gsod year, year, year, year tables. And you're specifying that as an alias, you're saying AS a. The reason why you're going to do that has become apparent in just a second. And you're joining that on a key which we're going to cover into the station's data, and we're saying that AS b. And why are we using these aliases a and b? It's because if there are ambiguous field names, like say the word name or say the word station is common across both tables, when you join these two together and you're adding new fields, you can't have any name collisions. So having an alias actually specifies where that comes from. And it's actually technically not required for a. field name if that name is unique across all the tables that you're joining. So that as the actual JOIN itself, now the condition is match these records, bring these tables together horizontally on the weather recordings which is a, a.station is equal to the b.USAF. And that's again, you can actually match on fields that have names that don't match. But again, it's just going to look at those values and that's the case here, we see the station name actually doesn't match the USAF name in the station's table. And also, you can have that additional piece of the JOIN key, a.wban=b.wban. So there's a lot going on there. And then of course, we apply a filter all the way at the bottom. That just does a bunch of different things. It filters for only US temperature readings and table data that's after 2015. And the US state is not null. But the main thing that we want to focus on right now is that JOIN condition. So there's a lot of things that are going on. One of the things that we haven't covered yet is what type of JOIN that we're actually doing to link these two things together. So let's do a quick review before we get into the different types of JOINs. The top table listed in the red box there, those are all the fields that we're pulling from the individual temperature recordings across all those different tables. And then the blue box, those are the fields that are coming from that separate table. Then notice we're listing them all together in that select statement. A general good best practice for you is, if you have table data that's coming from multi-different sources, you can actually break up in your select statement with a comment, basically saying "Oh, this is your temperature readings and then these are your station readings as well." That's purely for the readability of your code. And then capital JOIN is your join. We'll go on to the types in a minute. And then you're joining condition again, you can have more than one JOIN key.
