When we talked about cross joins, I said it's the default type of join for an unqualified join, which hopefully leads you to believe that there's such thing as a qualified join, which there is.

When it comes to a qualified join, the default type of join is an inner join, which is what we're gonna talk about now. An inner join takes two tables, a left and a right table, and it matches them up on something, on a qualification. That's why it's a qualified join. It matches them up on some qualification, usually two columns matching each other. Then any rows that don't have a match on either table are eliminated. Any rows on the left table that don't have a match on the right side, boom, gone. Any rows on the right side that don't have a match on the left side, boom, gone. Let's take a look at that now.

We're working with another table here. If we say select * from bookmarks and we'll limit it to 10, we see we've got a user ID and a URL. It's all made up information, but we can join the users and their bookmarks together by saying select * from users. We're gonna say that the left table is users and then we can just write the word join, join bookmarks. We need to introduce our qualifier, our condition, our joined condition here. We're gonna say on users.id equals bookmarks.user_id. If we do that, let's limit that down. Let's limit that down and we'll see that Kyleigh Runolfsson has a bunch of bookmarks over here. It does look like that matched up because if we look at the user ID 652655, when we come over here, Kyleigh's user ID is 652655. We did match them up correctly.

We can slim this down a little bit. Let's slim this down a little bit. We can use the qualified name here because we do have, if we were to say id, it's gonna say, ooh, I don't know what you're talking about man. Because we have two tables and they both have a column named id, we do have to introduce the table qualifier, which is totally fine. Users.id, and then we'll say users.first_name and then let's do bookmarks.user_id. We can see all of those right next to each other. There you go. When we switched to Wayland, we're down, Waylon, sorry, 652656. Those match up quite nicely.

Notice that we just wrote the word join. We just wrote the word join and then we had our condition at the end. We didn't have to write inner, outer, left, right, any of that stuff. Inner join is the default type of join. And lemme see if I can prove to you that rows that don't match up are eliminated. We don't need this duplicate because they are guaranteed to be the same. I'm gonna drop that off. We'll keep first_name. I do wanna put bookmarks.id on there and we'll put bookmarks.url on there as well. Limit it down to 10. And we're gonna take this ID and we're just gonna say where bookmarks.id equals that one. Because that's the one that we're gonna, too much space. That's the one that we're gonna monkey around with.

With this bookmark here, what happens if this bookmark becomes disassociated with the user, i.e. what happens if the user ID of this bookmark goes to null? Let's figure it out. We will do update bookmarks set user_id equal to null where id equals this one. If we run that, and now let's just read it back, select * from bookmarks where id is that. It's been disassociated with this user Waylon. If we tried to do an inner join again, it's just not there. Because it doesn't have a corresponding match in the user's table because it has no user ID. If we drop this where off, you'll see, oh, that was a Kyleigh URL. Sorry, Kyleigh, that was a Kyleigh URL and it is now gone. We do not see that anymore. It does still exist, anderson.org, but it no longer exists in this joined table because orphans have been eliminated.

There's one other syntax on this join condition that I wanna show you. I don't use it very often 'cause this is not how I name my columns. But if you name your columns this way, you can use this syntax, and the syntax is using.

Note: When I show you how to use "using", there's a small error in the query I show on the screen. The keyword "on" shouldn't be included in the query, so the correct query is:

select * from users join bookmarks using(user_id) limit 10;

Using is a shorthand. Here is our join condition right here. We said users.id equals bookmarks.user_id. Pretend with me for just one second that in the users table we actually named the primary key user_id, which a lot of people do. There's absolutely nothing wrong with that. A lot of people do that, that's totally fine. I like to name my primary keys id instead of table_id. That's just a personal preference thing.

Here's the situation. If you had this right here where both columns are named the same thing in both tables, you can get away with this. You can say using user_id and that's gonna say, great, I'm gonna look for that in both tables. The one benefit you get out of this is if you were to do a select *, it's not gonna bring back both user IDs because then you would have two columns and you'd run into that ambiguous thing again. If you have identically named columns that you are joining on and you use using, hard to say, and you use using, it's going to drop one of those for your select * when it comes back.

Inner join recap. It is the default type of conditional join. You don't have to write the word inner, but you can. If you want to be more clear, that's totally fine. You can also just write join. The condition goes at the end where you specify which columns should be used to join. We joined with column from each table. You can join with two, three, four, it doesn't matter as long as they match. When you have an inner join, it takes a left table and a right table. Using that join condition finds the rows that match, eliminates the ones that don't have matches, and then returns that result set to you.