Now then we're going to take a look at joins from an SQL standpoint. For our purposes, there are three kinds of joins. A join is basically how do we combine rows from two or more tables? If we have a table with some records in it and we have some related records in another table, how do we join those together? There's really three ways we do it to get you started and they're pretty straightforward. Just think of this as basic set logic like you learned in high school or even before. There's an inner join where I say, hey, I want all of the records that are common to both table 1 and table 2. Then there's a left join that says, hey, I want all the record out of table 1, all of them. I also want any matching records out of table 2. Then the right join is just the mirror image of that. Again, an inner join returns records that have matching values in both tables, a left outer join returns all the records from the left table and the matched records from the right table, and a right outer join is the mirror image of that, it returns all records from the right table and the matched records from the left table. We'll sometimes need to use an alias to refer to either tables or columns inside of our tables. The reason for this is that we have these two tables that relate to one another and they'll often, if not always, have at least one column name in common, that's how they, "Relate to one another." In this case, you'll notice we have a table bands and it has a band ID. Then you'll notice that on the right-hand side of your screen, we have a table concert, and while it has a concert ID, note that it also has a band ID. How would you do a query to say, well, I want this band ID in bands, and I want these records associated with the band ID in concert? The right way to do that is to use an alias. You can be as descriptive here as you want, we've kept it pretty simple. You'll notice that we call our bands, table b and our concert, table c, just to keep it very simple for you. It's a temporary name that we can use in this query. Then let's take a look at some examples. We're going to left join. Remember what left join means is take all of the records from the table on the left, that's our band table, so we should have these three records. Yes, we indeed have them. If you look down here, we have the Rolling Stones, we have the Chicks, and we have Lecrae. We want to left join those with concert on band ID. We're going to go out and we're going to get all the matching records aka what matches on band ID from our concert table. If you notice that, now you can see the join. We have band ID 101, the Rolling Stones, and they're in the arena, they're in the American Airlines Center, and they're in the Bomb Factory. Those are my related records from the concert table. You'll notice that the Chicks do not have a matching record here in the concert table, and so you'll notice that it's just null but it still included them because it was a left join and we get all of the records from our left table. Then you'll notice that Lecrae also matched up. One last thing to notice is that in the concert table, this record did not show up, this last record because it has no band, it has no matching records and we only join on band ID where there's a record in the left table, aka the band table. Now we're going to do the mirror image of that which doesn't always make sense and we're showing you some orphan records here but to get the concept. Notice our select statement is very similar. We still are doing the same thing, we're basing it on band ID and band name and venue and city, we're going to get a little bit more data , but from bands, but right join this time, on concert we're giving it the alias c where the two band IDs match up. Notice that a right join will give you all of the records from the right-hand table. It's going to start out with those and then it's going to join where there is a band ID that matches over here. If we look at our records that I returned this time, we get band ID, we get band name, we get venue, and we get city. From the concert, we're going to get all of those records, all of our records from the right-hand table because remember that's what a join does, a right join gets all the records from the right. What did those include? What were those records on the right? The Tabernacle and the Trees arenas. That's there, but notice that our band ID and our band name are null. Even though it gave us the records, it doesn't know what to do with them. Why doesn't it know what to do with them? Because if you look here, the Tabernacle has a band ID of 104 and there is no matching record here, so it doesn't know what to do. This is like, hey, wait a minute, you've scheduled a concert and you don't have a band. That could be a problem. You have something going on here. Then in the other case, same thing. We have Trees with a band ID of zero and there is no zero, so that's coming back with null for band ID and band name and this is obviously not a good situation, it's actually saying we have some orphan records over here, but for purposes of our discussion, we've done a right join. Then finally, an inner join. Again, notice the syntax. I didn't say inner join here. But if you just do join and you don't say right or left, the default is it will do an inner join. Remember what the inner join is, will be a join on the records that they have in common. In this case, we have three records in inner join. You'll notice as we go down through here in our results table, yes, I have the Rolling Stones, the Rolling Stones, the Rolling Stones, and I had matching records for the Rolling Stones, so that was no problem. I had them in the arena by that band ID, I had them in American Airlines Center by that ID, and I had them in the Bomb Factory by that ID. That was great. Lecrae also over here, band number 103, they had a matching record in the Accor Arena. However, there was no matching record for the Chicks. They have no concert scheduled, so they did not show up on my results. You'll notice there was no band for the Tabernacle or the Trees, so they did not show up on my results. It's only the inner joins, only the place that have matching records.
