Okay, so here's the dilemma. You have quite a few tables that you need to bring together. This is the syntax for the union, select all the fields we want from one of these particular tables. Union distinct, just write it out and then you just do another table and union distinct, do another table and union distinct, do another table. [LAUGH] And if you have more than 10 tables, your fingers are going to get tired of typing all these tables over time. So I don't really want to type a 100 unions and it's going to make my code just extremely long vertically to read. So it must be a better way. And now we're going to introduce the concept of a table wild card. This is pretty cool. So much like using that like operator when we looked at finding the charities that had helped in the name. You can actually operate over the table names and you're from statement and do a wildcard matching the union to you see this asterisk here, this is pretty cool. So let's cover the SQL syntax up there on the right. So the same thing, you're selecting the fields that you want from all the tables. And then you're adding the from clause the datasets there. And for the table, you see it begins with the prefix gsod and then there's an asterisk. So any tables that are in that dataset and its entirety that match that prefix are going to be included. And because the dataset is very original structured, so it's going to be year, year, year, year, for each of those different tables. It's going to bring together all of the historical weather data tables from 1929 to current. You're talking about a lot of millions and millions of records just with one single line of code there. All right, so what happens if you wanted to filter out for just a subset of the tables? So, say, you just wanted to find and match together all the temperatures from 1950 or after 1950. Now here's another reserved keyword that's specific to big query. You can use the table suffix to grab what you're actually matching on in that wild card. And then you can use that table suffix in normal SQL operations. So here you can say, all right, well, match everything but only include those tables that are after 1950 here. And of course, you normally want to be as granular or make that prefix as long as you can for performance reasons. Just you're limiting the amount that you're actually matching on. So table suffix in the wild card are two very useful concepts and that's the subject of our next key message. So if you have many, many, many, many unions across datasets that have labeled data tables with structured standardized names, make use of that table wildcard. And also what's really, really nice is this table suffix, you can use that to filter out the tables that are actually included. And in addition to filtering on that in your where clause you can actually return the table name in your select statement by just calling that stable suffix. Okay, so here are some pitfalls when it comes to doing unions inside of SQL. So keep in mind that duplicative records piece that we mentioned before. When you want to remove the duplicative records, when you're mashing multiple tables together, you'll be using a union distinct. And when you want to just have all the records, no matter if there are duplicate records across multiple tables, you'll be using that union all. Now it is required that you choose a distinct or an all option. So, as you might imagine, you're hoping that the way these temperature recordings were recorded over time that the field names haven't changed. Or the amount of fields that were required hasn't changed over time. Because if they do, when you're matching these tables together vertically. If you're trying to put tables that don't have the same amount of columns, you're going to get a mismatch and you're getting an error in your union. So it's very good to do a pre processing exercise to make sure that unlike what we saw in cloud data prep, where you can have fields that are present in one day to step down and the other. In a true SQL union, those tables need to match up exactly on the count of columns. And now you can do, technically, you can have different column names and match together based on what's called the index of where that column appears. But generally what I like to do just for my own sanity is make sure the names and the count of columns matched as well. Okay, so recap, we have joined, the union, rather, we've matched together all that historical data from the past all the way to the current. And that's a lot of temperature readings. But we still don't know where those temperature readings came from. And that's where we're actually going to be joining this massive amounts of consolidated temperature recordings with another data set that is just a single table on that station reading information.
