The course "Mastering Postgres" helped me practice query commands and understand why they're important. Inspiring me to explore the 'why' and 'how' behind each one. Truly a great course!Nakolus
Shorten dev cycles with branching and zero-downtime schema migrations.
Every query you have seen me write so far, unless I'm forgetting, every query you've seen me write has been selecting from a single table, because we've just tried to, we're trying to prove indexes, we're trying to prove data types, that kind of stuff. That's great. Selecting from a single table is pretty common and pretty important. But we're gonna keep going. We must go on.
We're gonna start putting tables together side by side. There is a way to put result sets together, one over the other, and we're gonna look at that a little further on. For now, we're gonna bring two tables together, side by side using joins. We are going to start with one that is not terribly common, but can be exactly what you're looking for in some cases, and that is the cross join.
A cross join is kind of wild. Let me show you. We've got select * from letters and we got five letters, select * from numbers, we got five numbers. If we do select * from letters, numbers, and just run it together with a comma, we get a cross join. A couple of things to look at here. The first is the number of results. The number of results is 25 rows. That is because we had two tables, and they each had five rows in them. That gives us 25 rows. It is a Cartesian product. Every row on the left hand gets matched up with every row on the right hand to produce this output here. We have A1 and then we have A2, we have A3, A4, and A5. Each letter got matched up with each number.
The other thing that we need to note here is that we can just run it together with commas like that because cross join is the default for an unqualified join, where it's just saying, "Hey, throw these two tables together. I'm not gonna give you any information on how you should link them up. I'm gonna tell you nothing." You could also write it like this if you wanted to be explicit, and you would get back the exact same thing. Maybe not the most, well, definitely not the most useful join, but it is useful when you're trying to create permutations and combinations.
Let's keep going, and I can show you some more cool stuff that we can do here. If we were to, instead of saying select *, what if we did select letter concat number, and then we were to throw an upper on that? Hey, now we're starting to get maybe some sort of seating assignment chart availability, maybe some sort of coupon codes, something like that. The frustrating part is that we had to generate, we had to create these tables just to hold the series. Aha, we did not. If we do select * from generate series, we can generate these series on the fly. We can do one to ten, one to a hundred. Let's keep it at one to ten. There are some numbers. How are we gonna get letters? I have one idea. What if we changed this to 65, 75? While those are still numbers, we're gonna say that this is, we'll call this letters and then we'll just say... We're saying that the table, this made up table from the generate series is named letters, and then the column is named L. If we select * on that, we'll see that we have changed the column name to L.
Now what if we did this? What if we threw a CHRL on it? And boom, now we have some ASCII characters here. So 65 is a capital A, and we turn that into a character like that. Let's give this one a name as numbers, numbers N, and then let's see if we can cross join these guys. We're gonna take you, we're gonna come down here, and we're gonna plop you right there with just a comma. Or we could of course do cross join. Let's do that for clarity's sake, and then that will be close. We're getting close, we have the, we have the cross join going on. Let's go ahead and combine it with the number as well. And there you go.
Let's just for fun, let's throw a parentheses around this and let's say as code. We have a generated on the fly set of codes. Maybe we wanna go up to a hundred and we want 65 plus 26, 91. You can't do that live. I'm not gonna do that live. We have A1 through A100, B. If we go all the way, let's go all the way down. Ah, we went one too far. We have opening left bracket, so that's fine. We can just drop that down. Now we have A1 through a hundred all the way down to Z. I gotta drag that all the way down to Z100.
I lured you in with the cross join, and we got to do a little bit of generate series as well. And you got to watch me execute a perfect off by one error. I hope you enjoyed that. Remember a cross join Cartesian product puts every row on the left hand, along with every row on the right hand. Can be useful for generating combinations like this.