Self Join

[00:00:00]
>> So, I found myself when I was doing the One of the part earlier where I gave myself the category name of Brian Holt. I was trying to find where there was an English, or there were there was a German word for something and not an English word for something.

[00:00:15]
So, I was like I can use SQL to find that. You can use SQL to Find something that has a German translation in a table but does not have an English translation in the same table. How do you do that? You are actually going to join a table to itself.

[00:00:33]
So, you can do what's called a self join. And I'm gonna show you how to do that really quick. So, reasonable question. Let's say you run this multinational movie database and you wanna go and make sure that everything has a translation in it. You can write a query that said like, give me all of the Italian things that don't have English translations or vice versa.

[00:00:53]
So, I'm gonna select c1.category id. C1 language As de language or de lang. So, we're gonna do C1 dot name as de name. C2 to language as English name en-lang And C2.name as en name. So, it's a bunch of things that we're selecting. From categories names c1. We're gonna do a LEFT JOIN because we're trying to find things that have For a language of de.

[00:01:57]
So, you wanna include those in the result set but don't have an English translation. So, you want to do a LEFT JOIN. We can also get it done in like a full OUTER JOIN. And then we will be able to see on both sides and just check where things are null.

[00:02:09]
We're not gonna do that. We're just looking for things that have German words that do not have English words. So, LEFT JOIN category names again. And we're just referencing the same table. There's no special magic to self joins. We're just calling it something else. And we have two versions of the same table.

[00:02:31]
On C1 dot category id = c2.category id. And C2.language= en, Where C2 dot language Is NULL. And C1.language is German, right? And we'll limit that to 20 or something like that. So what I did, is I asked the question, let's just break it. I recognize this is weird and confusing to look at a table Adjoining to itself.

[00:03:29]
But I joined the category tables to itself and on once on the left side I have all German categories on the right side I have all English categories. And I'm asking, join them together. Wherever there is both an English name and a German leg, eliminated from the result set.

[00:03:49]
Leave me with only things that have a German translation and what you are not able to find an English translation for. So, all of these things here they exist in the category names table with a German translation. But there's not a second row to represent the English translation of it.

[00:04:07]
So, this is easier to read. Just talking about it one more time. I'm treating this, as far as PostgreSQL cares these are two separate tables It does not care that it's actually the same table. So, that's why we have C1 and C2. The first thing what I do where here, I make sure that everything that is in the result set from the from from this C1.

[00:04:31]
This is just German words in the category names table. So, my first result set. My first table, if you will, is just German words. I am left joining the table. C2 ignore that it's the same thing. And in this set over here, it's just English words. Just German words just English words, right?

[00:04:58]
And then joining them on the category id. So, it's gonna join everything together and I'm saying eliminate where C2 is no, that means it came in from left join, right? So, that there was a German word, and there was no English word for it. Which means that there was no translation for it.

[00:05:18]
Little mind bending, but does that make sense? Yeah, Mark.
>> Could you not do that with the sub-group?
>> You would still have a join. So, no I don't think you could no matter what somewhere you're gonna have to have a join. To be really clear about this, this took me some time of me staring at my computer's like, how am I gonna do this?

[00:05:44]
So, that was a fun little adventure and joins. Nothing here is anything that you haven't seen before, it's just weird cuz it's the same thing.