Pretty much everything we've been doing so far has been selecting from a single table, which has been fine. We've learned a lot. I hope, we've learned a lot so far. We looked for a second at indexing joins and I showed you just as a tease what a subquery looks like, but now we're gonna look at both in more detail. This video specifically is gonna be about joins.

SQLite has all the joins you would expect and some that you don't. It has a left right join. Not a left join, not a right join, a left right join. Is it useful? I don't know, but it's interesting.

So in our setup, we've got a users gooey here because it's just gonna be a lot easier to scroll left and right and see all to the gooey here because it's just gonna be a lot easier to scroll left and right and see all of these columns joined together and it gives me the ability to comment here which, I don't know, I kinda like that. So first thing is, this is valid. Users comma bookmarks is valid. It is not going to give you what you want. In that we get 10 Dibberts, which is, I guess, first name and then you scroll over here and you get 10 bookmarks.

But these bookmarks don't belong to Dibbert. They belong to a bunch of random users. And the reason that this is bad news bears is because we haven't said on users.id equals bookmarks.userid. So now if we join those 2 together, we should see We'll expand this a little bit. We've got, userid 903-978 along with bookmark 9, bookmark user ID 903-978.

Let's narrow this down just a little bit. When you're joining 2 tables together, you'll likely end up with column names that are duplicates and so you can prefix it with the table identifier. Users dot id, we'll do first name because those are easier to remember than user IDs and then we will do bookmarks dot user ID just to make sure we're on the right path and URL. So if we select that, we'll see actually, let's move this guy next to the guy that it is supposed to match. And if we run that, look at that.

Those all match up nicely. And remember, if we drop that out, you get all kinds of weird nonsense stuff. So this is the user ID and this is the bookmark's user ID and they don't even get close to matching. It makes no sense. So you always wanna make sure that you specify the linking columns, how these things go together.

Now, what's the deal what's the deal with this weird comma syntax? This may be normal. It just looks weird to me. I don't I don't like it. So that expanded too much.

That gave us way too much space. We just have where it's an embarrassment of riches with the space here. Okay. We don't need that much space. I don't like this style.

Users comma bookmarks, I don't like it. This is, equivalent to users join bookmarks. So if we run that again, we do get the same thing. Yep, we get the same thing. This is equivalent to users inner join bookmarks.

That's what I prefer. I know that that might make me an old man. That's fine. I am getting old, but I like to see the word. I like to see the word inner.

I know that a join is an inner join. I know that a comma is an inner join. I just like to see it. So in this case, we've got 2 tables. We've got the left table and we've got the right table.

When we declare it as an inner join, here's what we're saying. There are some records in the left table. There are some records in the right table. I want you to match those up and only give me the results where there is a match in the left table and there is a match in the right table. So in this case, users that don't have bookmarks are not going to be returned in this result set ever.

Bookmarks that don't belong to a user are not going to be joined in this result set ever. So an inner join says give me left table, right table, put them together and only give me the records that have, a value for the left and right table. That's probably that's probably what you want in in many cases, but that's not what you want in every case. Sometimes you want all of the records from the left table regardless of if there is, if there are any records in the right table. The way that you do that is, you guessed it, you do left join bookmarks.

This will give us all of the users regardless of if they have any bookmarks, but if they have bookmarks, we're gonna bring it in. So let's do this. And then, it looks like Aleah has several bookmarks. What do we wanna do here? Let's update such that this first Aleah here has no more bookmarks.

So we'll do update bookmarks set user ID equal to one where user ID equals Aaliyah's ID. So we're just gonna like totally destroy all of her bookmarks. I wouldn't do this on your production data. But now we do we we run it back and we see that that user whose bookmarks we just destroyed, sorry Aaliyah, she still comes back in the result set because it is a left join. If we were to change this back to an inner join, she gone.

Well, actually, we we don't know that because it changed the order. So let's say, and first name equals Aaliyah. So, if we do that, we see the left join works and the inner join is gone. So because we changed it to an inner join, she must have records in the right table to show up in the result set. We can do it the other way around.

I don't find this to be useful that often. I'll be honest. I've been doing database stuff for a long time. I don't do right joins that often. A right join is exactly the opposite.

It will give you, all of the records from the right table regardless of if there is a record in the left table. So now if we right join, we see, wow, those bookmarks aren't really there. We we would expect to see something in the right table that's not in the left table and we would if we just said where users dot ID is null. And, this is gonna take a little bit of time to run here, but basically what we're doing is we're saying, hey, I wanna join both tables together, favoring the right table. Bring back all of the records from the right table even if there's no match in the left table.

And then what we do is we narrow it down for show me the ones that actually don't have a result in the left table at all. And so this is actually a pretty good way to find orphaned records. We could do it with a sub query, which we'll maybe do later, but this this works just fine. This is the result of a right join. You can do something called a left right join, which is basically a full join in some other databases.

And what this will do is this gives you all the records from the left table and all the records from the right table. It joins it up where it can, but it will give you, the orphans in both tables. So, it's gonna be kinda hard to see because we have so many records in here, but you can just type left right join. And if you do left right join, there is the left part. So we know that the left part worked.

And let's run this again where we say where users dot ID is null. And if we run that again, it's gonna take a second. But hopefully, this left right join will produce the same right join data, if it ever finishes. There you go. So this full this full join in SQLite is called a left right join, which is yeah, I mean, yeah, it makes enough sense to me.

So now you know the difference between left join, right join, inner join. You know some of the weird syntaxes like left right join. We talked in previous videos about indexing joins. You want your foreign keys to be indexed. We've talked about enabling foreign key constraints.

If that is important to you, you will want to do that. You can see I was able to delete Aaliyah and leave her book marks behind and nobody said a word and that's because foreign key constraints were not enabled, which may be fine, but you need to decide that for your application. Joins are incredibly useful, incredibly powerful when you have related data that you're trying to put together side by side. When you have data that you're trying to put together over under, that's when we start talking about unions and when you're trying to filter one table based on data in another table, that's when we start to reach for subqueries which is what we're gonna do next.