Let's work on joins. How do we link across multiple tables? 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. You can 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. You're selecting the fields that you want from both tables. You'll notice that a., b., we're going to get into that in just a second, from, and you see the union wildcard here and all of that historical gsod* year tables. 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. You're joining that on a key which we're going to cover into the stations data. We're saying that As b. Why are we using these aliases a and b? Is 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. It's actually technically not required for a. field name if that name is unique across all the tables that you're joining. That as the actual joint itself, now the condition is match these records, bringing these tables together horizontally, on the weather recordings, which is a, a. station, is equal to the b.usaf. That's again, you can actually match any 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 stations table. Also you can have that additional piece of the join key, a.wban equals b.wban. There's a lot going on there. 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. 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 and then we're actually doing to link these two things together. 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. 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 all different sources, you can actually break up in your select statement with a comma. Basically saying, "This is your temperature readings and then these are your station readings as well." That's purely for the readability of your code. Then capital J-O-I-N is your join, we're going into the types in a minute. Then you're joining condition again, you can have more than one join key.
